1

I would like excel to display the value from table array which has two matching cells com_cd and div_cd using INDEX and MATCH.

I have tried the following formula but it did not work.

=INDEX(K9:K53,MATCH(K3,I9:I53,0),MATCH(K4,J9:J53,0))

Here is a screenshot of the excel sheet with the desired result given according to com_cd and div_cd

div

Ram
  • 3,092
  • 10
  • 40
  • 56
Iftekhar
  • 335
  • 2
  • 11
  • 26

2 Answers2

1

Try an array formula (CTRL + SHIFT + ENTER) instead of Enter.

=INDEX(K9:K53,MATCH(K3&K4,I9:I53&J9:J53,0),1)

Not tested but should work.

Will edit later explaining our formula and reason why your formula doesn't work.

Vasim
  • 3,052
  • 3
  • 35
  • 56
1

Your column_num parameter on the INDEX function cannot sinply provide a secondary row criteria. You need a way to ensure a two column match on the row_num parameter and leave the column_num either blank or as 1 (there is only only column in K9:K53).

The standard formula for K5 should be,

=index(K9:K53, aggregate(15, 6, row(1:45)/((i9:i53=k3)*(j9:j53=k4)), 1))

... or,

=index(K9:K53, min(index(row(1:45)+((i9:i53<>k3)+(j9:j53<>k4))*1e99, , )))

The cell range K9:K53 has a total of 45 rows. The position within K9:K53 will be within ROW(1:45). The first formula forces any non-matching row into an #DIV/0! error state and the AGGREGATE¹ function uses option 6 to ignore errors while retrieving the smallest valid entry with the SMALL sub-function (e.g. 15). The second formula performs the same action by adding 1E+99 (a 1 followed by 99 zeroes) to any non-matching row and taking the smallest matching row with the MIN function.

      AGGREGATE two column match

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

  • As it stands, should the number of rows being referenced change (a not unlikely scenario), then the OP would have to redetermine to what the portion ROW(1:45) should become. If you pass K:K to INDEX, as opposed to K9:K53, then the ROW portion is simply ROW(K9:K53). And, in general, if the range is Range1, then the ROW portion is ROW(Range1), thus offering a simple and constant relationship between the two, without any further thought or manual amendments required should Range1 change. – XOR LX Jan 26 '16 at 08:26