CODEWITHSUNDEEP
excelexcel-formula
pb94
pb94

Reputation: 27

Index match formula matching within a range

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!

Upvotes: 2

Views: 4443

Answers (2)

VBasic2008
VBasic2008

Reputation: 54983

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,"",...).

Upvotes: 1

bosco_yip
bosco_yip

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))),"")

enter image description here

Upvotes: 0

Related Questions