look up 2 criteria and return column header

[jalifid11]

So, in my head, this is a little different that a standard Index/Match formula..


I have a name in one column and an answer in another column... the column headers have numbers in each..

What I'm trying to do is match the name, and the answer, and then tell me what the column header is..

1 2 3
Tommy Bread Milk Eggs
Johnny Eggs Milk Bread
Mikey Mile Bread Eggs


So if I was looking for Tommy and Bread, it should return the 2... Because Tommy and Milk are on the same line and it's in the #2 column

 

[offthelip]

I don't quite understand what you are trying to do with your look up ( I can't see if you are looking for tommy and Bread that milk has got anything to do with it) However with a simple match just looking for one item you can get the heading using this formula:
=OFFSET(A1,0,MATCH(A7,A3:D3,0)-1)

 

[Eric W]

If I understand correctly, then something like this:

	A	B	C	D	E	F	G	H	I
Name	Item	Header
Tommy	Bread	Milk	Eggs	Tommy	Milk
Johnny	Eggs	Milk	Bread
Mikey	Milk	Bread	Eggs

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=INDEX($B$1:$D$1,MATCH(G2,INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),0),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jalifid11]

I don't quite understand what you are trying to do with your look up ( I can't see if you are looking for tommy and Bread that milk has got anything to do with it) However with a simple match just looking for one item you can get the heading using this formula:
=OFFSET(A1,0,MATCH(A7,A3:D3,0)-1)

Thanks...

Milk doesn't have to do with anything, yet... But if I changed the criteria to look up Tommy and Milk, then it would return whatever the column name was for that one...