Matching two criteria using index function

In regular use, the process involves matching specific cell contents within a designated range. However, as we are utilizing an array formula, I implemented a small trick by employing a method that directs the formula to locate a specified value and inform me of its position. When both criteria match, the result is returned, similar to an Excel formula. Here, the value represented by “X” is substituted with one variable and the value represented by “Y” is substituted with another variable. The logic behind this process is that the formula will “index” the array you select. By specifying a row number and a column number, for instance, you can obtain the desired value. For example, if you select the first row and first column of the designated array, it will return the value indicated by “Z.


Solution:

Got it from the LearnExcel reddit

=INDEX(A1:D4,MATCH(1,(A1:A4=A11)*IF(((B1:B4=B11)+(C1:C4=B11))>1,1,((B1:B4=B11)+(C1:C4=B11))),0),4)
/u/newunit13

Frequently Asked Questions