Vlookup frustration!!!

[ojos_delangel]

Okay, SO, I finally have this data validation down to an
artform.....hahaha..HOWEVER, now I have to somehow create this
spreadsheet so that whatever item you choose looks up a value,
mulitplies it by the # of units requested (which is in the cell
adjacent to the item chosen) and then enters that amount into the cell
next to the # of units!

I thought I had this figured out, but it was entering amounts that were
not allocated to that item if it was entering an amount at all! I have
attached a little sample so that it shows what I attempted to explain.
PLEASE if anyone can possibly help me!

The formula I had was: =VLOOKUP(A23,I137:J162,2)*C23

however it was returning the value 35.00 when 1 unit was chosen

btw, the blank cell in the pull down is necessary so that the pulldown
doesn't show if nothing is chosen.

Thank you!!!
Nat

 

[Gord Dibben]

Nat

You may have to qualify your lookup with another argument, True or False

From Help on VLOOKUP.......

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an
approximate match is returned. In other words, if an exact match is not found,
the next largest value that is less than lookup_value is returned. If FALSE,
VLOOKUP will find an exact match. If one is not found, the error value #N/A is
returned............

If you are not getting an exact match, it is possible one does not exist, so
Excel uses the "approximate" match.

Re-write as.....=VLOOKUP(A23,I137:J162,2,FALSE)*C23

Or if you want to trap the #N/A use.....all one line

=IF(ISNA(VLOOKUP(A23,I137:J162,2,FALSE)*C23),"",VLOOKUP(A23,I137:J162,2,FALSE)*C23)

Gord Dibben XL2002