Reputation: 27
I've been tinkering with an index match formula for a set of data structured like so
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!
Upvotes: 2
Views: 4443
Reputation: 54983
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?
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...?=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...?IF(COUNTIF($B$1:$F$6,$I7)=0,"",...)
.Upvotes: 1
Reputation: 3802
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))),"")
Upvotes: 0