Lookup

F

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?
 
P

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
 
F

Fred

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

Pete_UK said:
Try this:

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

I think this is what you require.

Hope this helps.

Pete
 
P

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
 
F

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?
 
P

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
 
F

Fred

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

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

Thank you!
 
P

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
 
F

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top