Sequentially search multiple lists in Excel?

[axelgeorge]

Hi Excel fans,

I'm puzzling over a problem, and I hope you guys can help. Can you think of a way to do the following...

Suppose I'm a grocer and I use a spreadsheet to track my inventory. I sell fruits, vegetables, and dried spices. On one worksheet in my Excel file, I create the following lists:

[TABLE="width: 500"]
<tbody>[TR]
[TD]FRUITS
[/TD]
[TD]VEGETABLES
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]Potato
[/TD]
[/TR]
[TR]
[TD]Orange
[/TD]
[TD]Celery
[/TD]
[/TR]
[TR]
[TD]Banana
[/TD]
[TD]Cucumber
[/TD]
[/TR]
[TR]
[TD]Grape
[/TD]
[TD]Radish
[/TD]
[/TR]
[TR]
[TD]Pear
[/TD]
[TD]Broccoli
[/TD]
[/TR]
</tbody>[/TABLE]

Then on a second worksheet, I have my actual inventory, which is this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ITEM
[/TD]
[TD]QNTY
[/TD]
[TD]PRICE
[/TD]
[TD]CLASSIFICATION
[/TD]
[/TR]
[TR]
[TD]Banana
[/TD]
[TD]50
[/TD]
[TD]$.25
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cucumber
[/TD]
[TD]100
[/TD]
[TD]$1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dried Oregano
[/TD]
[TD]30
[/TD]
[TD]$5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Broccoli
[/TD]
[TD]100
[/TD]
[TD]$2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Radish
[/TD]
[TD]40
[/TD]
[TD]$1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a formula for the "Classification" cells of the Inventory. For each "Classification" cell, I want the formula to do the following:

1. Search for the corresponding "Item" in the "Fruits" list; if found, then put "FRUIT" in the Classification field.
2. If not found, search for the corresponding "Item" in the "Vegetables" list; if found, then put "VEGETABLE" in the Classification field.
3. If still not found, put "SPICES" in the Classification field.

So using my toy example:

• The formula for the first row would search for "Banana" in the "Fruits" list. Because the desired text sting is found, the formula enters "FRUIT" into the Classification field.
• The formula for the next row would search for "Cucumber" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is found there, the formula enters "VEGETABLE" into the Classification field.
• The formula for the next row would search for "Dried Oregano" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is not found there, the formula enters "SPICES" into the Classification field.

Does this make sense? I have no idea if Excel can handle this level of automation, but I suspect there must be a way to do this.

Many thanks!
-G

 

[ChrisM]

Really the best way to do this is to put all your items in one list, with an additional column to describe the category. Then you could do a simple vlookup and you'd be all done.

But, if you must go this route, try:

=IFERROR(VLOOKUP(K14,I$8:J$10,2,0),IFERROR(VLOOKUP(K14,L$8:M$10,2,0),"Spices"))

Where:

K14 is the lookup value

I8:J10 is list #1

L8:M10 is list #2

 

[axelgeorge]

Great! Many thanks, let me try this out...!

-G