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

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

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

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

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