VLOOKUP Question

S

Susan

Hello All,

Problem with a VLOOKUP. EX: When "Elmer Fudd" is chosen from the drop down
in a spreadsheet, EF should auto appear in the appropriate colmun. But "AP"
for APPLE PIE shows up. All others work.
Formula is =IF(C2=0," ",(VLOOKUP(C2,$AM$1:$AN$11,2)))
COLUMN AM COLUMN AN
ROW1 APPLE PIE AP
2 GREEN BEANS GB
3 RED APPLES RB
4 ORANGE CARROTS OC
5 LIMA BEANS LB
6 SNOW WHITE SW
7 BUGS BUNNY BB
8 ELMER FUDD EF

Thank you everyone!
COLUMN AM HOLDS THE NAME AND COLUMN AN HOLDS THE INITIALS
 
J

Jim Cone

Add comma zero after the last number...
=IF(C2=0," ",(VLOOKUP(C2,$AM$1:$AN$11,2,0)))
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Susan" <[email protected]>
wrote in message
Hello All,
Problem with a VLOOKUP. EX: When "Elmer Fudd" is chosen from the drop down
in a spreadsheet, EF should auto appear in the appropriate colmun. But "AP"
for APPLE PIE shows up. All others work.
Formula is =IF(C2=0," ",(VLOOKUP(C2,$AM$1:$AN$11,2)))
COLUMN AM COLUMN AN
ROW1 APPLE PIE AP
2 GREEN BEANS GB
3 RED APPLES RB
4 ORANGE CARROTS OC
5 LIMA BEANS LB
6 SNOW WHITE SW
7 BUGS BUNNY BB
8 ELMER FUDD EF

Thank you everyone!
COLUMN AM HOLDS THE NAME AND COLUMN AN HOLDS THE INITIALS
 
B

BoniM

You should also be getting a wrong answer for orange carrots, lima beans, and
bugs bunny... with the way you have the vlookup set up, it's looking for the
closest match. In order to get the right answer, your data must be sort in
ascending order. Once it finds a match that exceeds the lookup value, it
gives the previous row as the answer. You get AP for Elmer Fudd, because
Elmer Fudd would fall between Apple Pie and Green Beans, so AP is the closest
match. You should get GB for Orange Carrots and Lima Beans and AP for Bugs
Bunny for the same reason.
You can reorder your data or change your formula to only find an exact match:
=IF(C2=0,"",(VLOOKUP(C2,$AM$1:$AN$11,2,0)))
 
S

Susan

Many thanks to you BoniM for taking time to help

BoniM said:
You should also be getting a wrong answer for orange carrots, lima beans, and
bugs bunny... with the way you have the vlookup set up, it's looking for the
closest match. In order to get the right answer, your data must be sort in
ascending order. Once it finds a match that exceeds the lookup value, it
gives the previous row as the answer. You get AP for Elmer Fudd, because
Elmer Fudd would fall between Apple Pie and Green Beans, so AP is the closest
match. You should get GB for Orange Carrots and Lima Beans and AP for Bugs
Bunny for the same reason.
You can reorder your data or change your formula to only find an exact match:
=IF(C2=0,"",(VLOOKUP(C2,$AM$1:$AN$11,2,0)))
Click to expand...
 
Top