I am trying to lookup values in a row within a table (*below). This table is
For a 'range' which a returned value must fall into as follows:

A B
2.99 0.00
3.00 2.00
4.99 2.00
5.00 4.00
8.99 4.00
9.00 6.00

For Example if a row has the values: I12 1.84 M12 3.01 O12 2.98 I need to
return the value of 2.00.

Will a lookup tabl eor if stmts work, Any thoughts?

Try this:

=VLOOKUP(MAX(I12,M12,O12),A$1:B$6,2)

I think this is what you require.

Hope this helps.

Pete

On Sep 23, 2:57*pm, Fred wrote:
I am trying to lookup values in a row within a table (*below). *This table is
For a 'range' which a returned value must fall into as follows:

A * * * * * * *B
2.99 * *0.00
3.00 * *2.00
4.99 * *2.00
5.00 * *4.00
8.99 * *4.00
9.00 * *6.00

For Example if a row has the values: I12 1.84 * M12 3.01 O12 2.98 *I need to
return the value of 2.00.

Will a lookup tabl eor if stmts work, Any thoughts?

Receiving a #N/A error but we're closer!

"Pete_UK" wrote:

Try this:

=VLOOKUP(MAX(I12,M12,O12),A$1:B$6,2)

I think this is what you require.

Hope this helps.

Pete

On Sep 23, 2:57 pm, Fred wrote:
I am trying to lookup values in a row within a table (*below). This table is
For a 'range' which a returned value must fall into as follows:

A B
2.99 0.00
3.00 2.00
4.99 2.00
5.00 4.00
8.99 4.00
9.00 6.00

For Example if a row has the values: I12 1.84 M12 3.01 O12 2.98 I need to
return the value of 2.00.

Will a lookup tabl eor if stmts work, Any thoughts?

Is the maximum value of those 3 cells less than 2.99, so that it is
outside the range of the table?

Pete

On Sep 23, 4:18*pm, Fred wrote:
Receiving a #N/A error but we're closer!



"Pete_UK" wrote:
Try this:

=VLOOKUP(MAX(I12,M12,O12),A$1:B$6,2)

I think this is what you require.

Hope this helps.

Pete

On Sep 23, 2:57 pm, Fred wrote:
I am trying to lookup values in a row within a table (*below). *This table is
For a 'range' which a returned value must fall into as follows:

A * * * * * * *B
2.99 * *0.00
3.00 * *2.00
4.99 * *2.00
5.00 * *4.00
8.99 * *4.00
9.00 * *6.00

For Example if a row has the values: I12 1.84 * M12 3.01 O12 2.98 *I need to
return the value of 2.00.

Will a lookup tabl eor if stmts work, Any thoughts?- Hide quoted text -

- Show quoted text -

Actually, if I compare only one number to those within the table as follows:

8.70 (*Again less than 8.99 but greater than 5.00)

Will the formula change to = vlookup(O8,A$1:B$6,2)?

And yes how do I work a number outside the range?

"Pete_UK" wrote:

Is the maximum value of those 3 cells less than 2.99, so that it is
outside the range of the table?

Pete

On Sep 23, 4:18 pm, Fred wrote:
Receiving a #N/A error but we're closer!



"Pete_UK" wrote:
Try this:

=VLOOKUP(MAX(I12,M12,O12),A$1:B$6,2)

I think this is what you require.

Hope this helps.

Pete

On Sep 23, 2:57 pm, Fred wrote:
I am trying to lookup values in a row within a table (*below). This table is
For a 'range' which a returned value must fall into as follows:

A B
2.99 0.00
3.00 2.00
4.99 2.00
5.00 4.00
8.99 4.00
9.00 6.00

For Example if a row has the values: I12 1.84 M12 3.01 O12 2.98 I need to
return the value of 2.00.

Will a lookup tabl eor if stmts work, Any thoughts?- Hide quoted text -

- Show quoted text -

Yes, that is correct, although you now seem to be using row 8 rather
than 12.

If the number is greater than 9 (at one end of the range) the formula
will still pick up the corresponding value from 9, i.e. 6.00. But, if
the value is less than 2.99 (at the other end of the range), it will
give rise to that error. There are a few things that you could do, eg:

=VLOOKUP(MAX(I8,M8,O8,2.99),A$1:B$6,2)

will choose 2.99 as the lookup value if the other values are all less
than this, so it will choose the lowest value from your table.

Another way would be:

= IF(O8<A$1,"out of range",VLOOKUP(O8,A$1:B$6,2))

(not using the MAX function here), where you could change the "out of
range" to some other value (like -1).

Alternatively still, you could insert a new row at the top of your
table and put in a lower value there, like:

A B
0.00 0.00
2.99 0.00
3.00 2.00
4.99 2.00
5.00 4.00
8.99 4.00
9.00 6.00

and then the formula would become:

= VLOOKUP(O8,A$1:B$7,2)

Hope this helps.

Pete


On Sep 23, 5:25*pm, Fred wrote:
Actually, if I compare only one number to those within the table as follows:

8.70 *(*Again less than 8.99 but greater than 5.00)

Will the formula change to = vlookup(O8,A$1:B$6,2)?

And yes how do I work a number outside the range?



"Pete_UK" wrote:
Is the maximum value of those 3 cells less than 2.99, so that it is
outside the range of the table?

Pete

On Sep 23, 4:18 pm, Fred wrote:
Receiving a #N/A error but we're closer!

"Pete_UK" wrote:
Try this:

=VLOOKUP(MAX(I12,M12,O12),A$1:B$6,2)

I think this is what you require.

Hope this helps.

Pete

On Sep 23, 2:57 pm, Fred wrote:
I am trying to lookup values in a row within a table (*below). *This table is
For a 'range' which a returned value must fall into as follows:

A * * * * * * *B
2.99 * *0.00
3.00 * *2.00
4.99 * *2.00
5.00 * *4.00
8.99 * *4.00
9.00 * *6.00

For Example if a row has the values: I12 1.84 * M12 3.01 O12 2.98 *I need to
return the value of 2.00.

Will a lookup tabl eor if stmts work, Any thoughts?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -