Martin this is absolutely perfect! Thank you very much!

By the way, the data on the actual spreadsheet is obviously a lot more
complex than fruits and vegetables, plus it consists of 9 tabs so
combining it all onto one sheet would be a nightmare... but trust me I
did consider it.

Thanks again to both of you,

Dan

Martin Fishlock wrote:
Dan,

It is a bit of a mouthfull but:


=IF($A$2="F",
SUMPRODUCT(
--($B$2=Sheet1!A2:A6),
--($C$2=Sheet1!B2:B6),
--(Sheet1!C2:C6)
),
SUMPRODUCT(
--($B$2=Sheet2!A2:A5),
--($C$2=Sheet2!B2:B5),
--(Sheet2!C2:C5)
)
)

An easier solution is to have the whole products on one sheet

with a=type, b=product, c=color, e=code
and in d do a1 & ":" & b1 & ":" & c1
and then use vlookup
as in vlookup(a1,sheet4!d:e,2,false) and that would match it.





--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"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