Lookup

[Fred]

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?

 

[Pete_UK]

Try this:

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

I think this is what you require.

Hope this helps.

Pete

 

[Fred]

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

Try this:

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

I think this is what you require.

Hope this helps.

Pete

 

[Pete_UK]

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

Pete

 

[Fred]

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]

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

 

[Fred]

Closer, but I'm receiving 0.00 as the result?

Here is the formula:
=VLOOKUP(MAX(I7,K7,M7,2.99),O$38$43,2)

Thank you!

 

[Pete_UK]

It might be that those "numbers" (either in your table or in cells I7,
K7 and M7 - you keep changing the row!!), are not really numbers but
are text values. If it is the 3 individual cells then the MAX function
will return 2.99 so the VLOOKUP function will return 0 - test this out
by changing the 2.99 to another value in your table and see what you
get. In order to find a match the numbers must be proper numbers.

Hope this helps.

Pete

 

[Fred]

Hi Pete,

I'm actually going to compare one value to the same table.

=VLOOKUP(H8,I$290:J$296,2)

I J
0.00 0.00
0.03 0.00
0.03 0.02
0.05 0.02
0.05 0.04
0.09 0.04
0.09 0.06

But if I have a negative value, how do I adjust my table or do I add, 2.99
within the formula?

Thanks,
Fred

 

[Pete_UK]

Your table seems to have changed quite a bit from the one you first
posted, and now you have duplicate values in column I - VLOOKUP will
only find the first of these.

Anyway, you could do this to trap -ve values:

=IF(H8<0,0,VLOOKUP(H8,I$290:J$296,2))

This will return 0 - I'm not sure what the 2.99 refers to now.

Hope this helps.

Pete