Return Headers from Matches in a List

[djc123]

Hi guys,

I have a list of ingredients on Sheet2 (B4-B6) and I want any Menu Items that contain at least 1 of these ingredients to be returned in column C (C4 onwards).

The list of ingredients are in the header of the same sheet (Row E onwards), with all subsiqent Menu Items listed in each collumn. If it is easier to return the Menu Item as a header, that is displayed in reverse on Sheet1.

I've looked at INDEX and MATCH formulas for for the life of me can't get it to work.

Any help is greatly appreciated.

Danny

[Glenn Kennedy]

I am wondering if you have made a mistake somewhere, in your formulae, or in your description of the problem...

For ANY product that has clashing CK Items, please manually add expected answers in C4 downwards....

[djc123]

Hi Glenn,

Apologies for the confusion.

I have added the results I would like in column C. All of these items either have Mayonnaise, Sliced Ham or Emmentaler Cheese in them.

On the 'Menu Items' Sheet, all of the said ingredients are highlighted in red, I would like the header of each collumn to appear in the list in column C.

I hope that helps,

Danny

[Glenn Kennedy]

OK. I am out for a couple of hours, but now know what you want.

[djc123]

Ok thanks pal, looking forward to your message

[Glenn Kennedy]

Try this out:

=IFERROR(INDEX('Menu Items'!$1:$1,AGGREGATE(15,6,COLUMN('Menu Items'!$A$2:$AR$20)/((ISNUMBER(MATCH('Menu Items'!$A$2:$AR$20,CK_Items,0)))*(COUNTIF($C$3:C3,'Menu Items'!$A$1:$AR$1)=0)),1)),"")

see sheet.