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" <dturner4_1999@yahoo.com> wrote in message
news:%23UWR6JbgFHA.1044@tk2msftngp13.phx.gbl...
> 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" <dturner4_1999@yahoo.com> wrote in message
news:OxIFFWbgFHA.2156@TK2MSFTNGP14.phx.gbl...
> 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