Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Banging head against wall with VLOOKUP
I have a workbook I use to populate certain cells below a chosen item from
a data validation dropdown. It works fine until I change one of the items in the first column of the named range that VLOOKUP looks at, then I get unexpected results (wrong food items). If I change 1st Meat item, 2nd meat item returns #NA in cells below it, otherwise only the changed Meat item gets wrong items in cells below. In either case others remain uneffected. Dropdowns are in F10 and F32 Current formulas in the 4 cells below F10: =IF(F$10="","",VLOOKUP(F$10,Items,2)) =IF(F$10="","",VLOOKUP(F$10,Items,3)) =IF(F$10="","",VLOOKUP(F$10,Items,4)) =IF(F$10="","",VLOOKUP(F$10,Items,5)) Substitute F32 for F10 in 4 cells below F32. 'Items' refers to a table of food items on another sheet: ='Food Groups'!$A$2:$E$12 Data Validation dropdown refers to List =Meat in range: ='Food Groups'!$A$2:$A$12 If I change anything in Col1 of Items (the Meat range), that's when trouble occurs. If I change anything in Cols 2-5, I get expected new returns from those cells. Help me save my sanity. -- David |
#2
|
|||
|
|||
Unless you add the argument, like I show here, you must have your values
sorted: =IF(F$10="","",VLOOKUP(F$10,Items,2,FALSE)) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "David" wrote in message ... I have a workbook I use to populate certain cells below a chosen item from a data validation dropdown. It works fine until I change one of the items in the first column of the named range that VLOOKUP looks at, then I get unexpected results (wrong food items). If I change 1st Meat item, 2nd meat item returns #NA in cells below it, otherwise only the changed Meat item gets wrong items in cells below. In either case others remain uneffected. Dropdowns are in F10 and F32 Current formulas in the 4 cells below F10: =IF(F$10="","",VLOOKUP(F$10,Items,2)) =IF(F$10="","",VLOOKUP(F$10,Items,3)) =IF(F$10="","",VLOOKUP(F$10,Items,4)) =IF(F$10="","",VLOOKUP(F$10,Items,5)) Substitute F32 for F10 in 4 cells below F32. 'Items' refers to a table of food items on another sheet: ='Food Groups'!$A$2:$E$12 Data Validation dropdown refers to List =Meat in range: ='Food Groups'!$A$2:$A$12 If I change anything in Col1 of Items (the Meat range), that's when trouble occurs. If I change anything in Cols 2-5, I get expected new returns from those cells. Help me save my sanity. -- David |
#3
|
|||
|
|||
Anne Troy wrote
Unless you add the argument, like I show here, you must have your values sorted: =IF(F$10="","",VLOOKUP(F$10,Items,2,FALSE)) Bingo! Now things work. I wasn't aware of what the FALSE argument referred to. Many thanks. -- David |
#4
|
|||
|
|||
You want to use false with text. With numbers, you only want to use false if
you need to find an exact number (an account number, for instance) rather than a value (such as doing a lookup of an age in an insurance rates price quote when there's not a specific value for EACH number; or for Grades when you want 60 to 70 to be a C....etc. This might help, too: http://www.officearticles.com/excel/...soft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "David" wrote in message ... Anne Troy wrote Unless you add the argument, like I show here, you must have your values sorted: =IF(F$10="","",VLOOKUP(F$10,Items,2,FALSE)) Bingo! Now things work. I wasn't aware of what the FALSE argument referred to. Many thanks. -- David |
#5
|
|||
|
|||
Anne Troy wrote
You want to use false with text. With numbers, you only want to use false if you need to find an exact number (an account number, for instance) rather than a value (such as doing a lookup of an age in an insurance rates price quote when there's not a specific value for EACH number; or for Grades when you want 60 to 70 to be a C....etc. This might help, too: http://www.officearticles.com/excel/...icrosoft_excel .htm Extra mile award to you. And I can cancel my appointment with the psychiatrist. -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Vlookup and Hlookup | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |