Horizontal Index Match (Hlookup multiple criterias)

[eyeope]

Hi,

I'm looking for some help on an HLOOKUP / INDEX MATCH with multiple criterias.

Attached a spreadsheet.

(In the lookup tab) - In B2 I want to look if A2 has any of the values that are in row 1 from the data tab.

For example in the lookup tab in A2 (Fredrick Herderngarden), if I manually go to the data tab I can see that Fredrick Herderngarden has three categories: Business&Finance, Premium, Lifestyle.

I'd like to automate this since the data is unstructured, otherwise I could solve it with an HLOOKUP.

Many thanks,

[xlnitwit]

Hi,

What do you want as the returned value? Yes, True, something else? For a simple True/False you might use
=ISNUMBER(MATCH(B$1,INDEX(data!$B$2:$D$5,MATCH($A2,data!$A$2:$A$5,0),0),0))

[eyeope]

Thanks! Yes/True would be awesome!

[xlnitwit]

Please see my edit above in that case.

[canapone]

Hi,

a first approach

in B2 to be copied across

=IF(SUMPRODUCT((data!$A$2:$A$100=$A2)*(data!$B$2:$D$100=B$1)),"Ciao!","")


Regards

Edit: Hi xlnitwit, sorry: did not mean to overlap...