Return Header based on a lookup of row and value within row

**lelaina** · 03-03-2011, 01:32 PM

Hi all,

I have been a lurker of this forum for a few years and always managed to find the answer to my questions by perusing old posts. After hours of trying different index/match/sumif variations, then searching for an answer, I decided it was time to finally post a question.

I am trying to return the column heading (or column number) based on the intersection of a particular row and a particular value within that row. I've tried to illustrate the problem with a table below, obviously not the model I am working with, but should give you the idea.

I need to lookup "Row 3", the value 10, and return "Header 5", etc.

While the row value and header values are unique, the values that are populated in the table may repeat, as illustrated below.

Any ideas??

Header 1 Header 2 Header 3 Header 4 Header 5 Header 6
Row 1 1 2 3 4 5 6
Row 2 0 0 5 6 7 8
Row 3 0 0 0 9 10 11
Row 4 0 1 2 3 4 5
Row 5 1 2 3 4 0 0
Row 6 0 0 0 6 7 8

**NBVC** · 03-03-2011, 01:46 PM

Assuming your data is in A1:G7, try:

=INDEX($B$1:$G$1,MATCH(10,INDEX($B$2:$G$7,MATCH("Row 3",$A$2:$A$7,0),0),0))

adjust as necessary.

**brokenbiscuits** · 03-03-2011, 01:46 PM

See attached for example.

The main issue would be if there were any repeated values within the same row, as you would only be able to bring back the first matching header. So for instance, putting in a value of 0 brings back Header 1, but 0 also appears in cols for Header 2 and 3 for Row 3.

**lelaina** · 03-03-2011, 02:10 PM

Thank you for such prompt responses!

I started with NBVC's solution (which was very similar to what I was trying earlier) but it isn't working.

NBVC - did you intend for the 2nd Index to have a column num 0?

I'll try brokenbiscuits next...

**NBVC** · 03-03-2011, 02:20 PM

Yes the 0 is meant to be there. It forces the INDEX function to return the range.

See attached.

**lelaina** · 03-03-2011, 02:57 PM

Thank you! I was actually finally able to get both ways to work... saved me several hours of angst this afternoon!

**hcorbitt** · 07-23-2014, 06:41 PM

I need to do the same thing, but as one of you mentioned this only returns the first header matching the value. Is there any other way to do this so I can return all of the matching headers?

Holly