Not using code you will need to add a helper column on each sheet, select
column a and insert new column. Next concantenate each row with code in
column A2 as =B2 & B3 and copy this formula down, this creates a food/color
combo. You can hide bothe of those columns if you need to. Now you need a
formula on sheet 3, your data showed that you skipped column C so I did as
well.
=IF(A2="fruit",VLOOKUP(B2&D2,Sheet1!A2:D24,4,FALSE ),IF(A2="Vegetable",VLOOKUP(B2&D2,Sheet2!A2:D22,4, FALSE),""))

I would use drop down boxes for selections because any misspellings will
result in an error
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Dan Oakes" wrote:

I've been trying to come up with a formula/macro to do this for a
long time but I can't seem to figure it out. You all seem very
knowledgeable so maybe you can take a stab at it.

Here's an example of what my spreadsheet looks like: (explanation
below)

Sheet1: [Fruit]
Column A Column B Column C
ITEM (duplicates) COLOR (duplicates) PRODUCT # (only 1)
Apples Green 1111
Apples Red 1112
Apples Pink 1113
Grapes Red 1114
Grapes Green 1115
-------------------------------------------------------
Sheet2: [Vegetables]
Column A Column B Column C
ITEM (duplicates) COLOR (duplicates) PRODUCT # (only 1)
Lettuce Red 2221
Lettuce Green 2222
Potatoes Brown 2223
Potatoes Red 2224
-------------------------------------------------------
Sheet3: [Look-Up]
Column A Column B Column C Column D
FRUIT/VEGETABLE ITEM COLOR PRODUCT #
[user defined] [user defined] [user defined] <FORMULA HERE

I want the user to input these 3 values and then the formula will
generate the PRODUCT #. I've tried countless Count/Index/Match
combinations but none seem to work; any suggestions? Sorry for the
lengthy explanation...

Thanks - Dan