Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question
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" 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question
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))) "Susan" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question
Many thanks to you BoniM for taking time to help
"BoniM" wrote: 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))) "Susan" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question
Thank you Jim for your time. Your help is much appreicated.
"Jim Cone" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup question | Excel Worksheet Functions | |||
VLOOKUP question | Excel Discussion (Misc queries) | |||
Vlookup question | Excel Discussion (Misc queries) | |||
VLOOKUP question | Excel Discussion (Misc queries) | |||
VLOOKUP question | Excel Discussion (Misc queries) |