INDEX MATCH based on 2 criteria and to return multiple values

Copper Contributor

Hi all, 


I am having difficulty putting two logic together, that is  have a formula that works to return the data from 2 criteria using simple Match logic, BUT having difficulty using multiple return values in row logic with it. 


This is where I have got.... 

I am looking to return a row of data (but lets just concentrate on the cell) whereby lookup value is "x" in Health Services Column C AND "" (blank) in Column K. I then wish to drag this down row by row until #N/A.... 


=INDEX('Health Services'!D$5:D1159,SMALL(IF(and($A$1='Health Services'!$C:$C,ROW('Health Services'!$C:$C),$A$2='Health Services'!$K:$K,ROW('Health Services'!$K:$K))-ROW($B$5)+1),ROW(2:2)))


Any suggestions on what parts of the logic I am missing?


Thank you! 


1 Reply

Hi Sarah,


It looks like you incorrectly check second criteria. There are few approaches to extract rows from the list based on multiple criteria, e.g.


Based on second one the formula could be

=INDEX($C$5:$Z$14, SMALL(IF(COUNTIF($A$1,$C$5:$C$14)*ISBLANK($K$5:$K$14), ROW($D$5:$Z$14)-MIN(ROW($D$5:$D$14))+1), ROW(A1)), COLUMN(A1)+1)

for the range as in attached sample