Multiple Sheet, Multiple Criteria Look-Up Function Help

[Dan Oakes]

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

 

[Dan Oakes]

**Click on Show Options/Show Original for a properly aligned version**

 

[Martin Fishlock]

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.

 

[Martin Fishlock]

I forgot to say that it is an array function so you have to do
ctrl+shft+enter on the cell.

 

[John Bundy]

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!A224,4,FALSE),IF(A2="Vegetable",VLOOKUP(B2&D2,Sheet2!A222,4,FALSE),""))

I would use drop down boxes for selections because any misspellings will
result in an error

 

[Dan Oakes]

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

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.

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