Index match formula matching within a range

Question

I've been tinkering with an index match formula for a set of data structured like so

Data

Currently my formula looks like this, but of course it's only matching against the one row. =INDEX(B7:F13,MATCH(H7,A7:A12,0),MATCH(I7,B1:F1,0)

I'd like for the following formula to work as below and match against all of the header rows, but I don't believe match has this functionality. Lookup formulas are also restricted to either 1 row or 1 column.

=INDEX(B7:F13,MATCH(H7,A7:A13,0),MATCH(I7,B1:F6,0)

Any help or suggestions would be appreciated!

Answer 1

Return Range Column of Cell Where Value Was Found

In cell J7 use the following array formula:

=IFERROR(IF(COUNTIF($B$1:$F$6,$I7)=0,"",INDEX($B$7:$F$12,MATCH($H7,$A$7:$A$12,0),MIN(IF($B$1:$F$6=$I7,COLUMN($A:$E))))),"")

The title of this post is referring to this array formula:

=MIN(IF($B$1:$F$6=$I7,COLUMN($A:$E)) 

which returns the column number needed for the INDEX function.

Where is the error when you need one?

• There could be matches in several columns. The MIN formula returns the left-most (MAX instead would return the right-most) column of a match. If there is no match, the MIN formula 'kindly' returns 0. Where is the error...?
• Luckily =INDEX($B$7:$F$12,1,0) will return a #VALUE! error. Or will it? Unfortunately, it won't because =INDEX($B$7:$F$12,1,0) entered as an array formula 'kindly' returns the first value, in this example, of the first row. Where is the error...?
• So I had to include the miserable IF(COUNTIF($B$1:$F$6,$I7)=0,"",...).

Answer 2

It J7, formula copied down :

=IFERROR(INDEX($B$7:$F$12,MATCH(H7,$A$7:$A$12,0),SUMPRODUCT(COLUMN($A$1:$E$1)*($B$1:$F$6=I7))),"")