INDEX MATCH based on 2 criteria and to return multiple values

[Sarah Glassie]

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! 

Sarah

[#2]

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.

https://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

https://www.get-digital-help.com/2010/02/11/match-two-criteria-and-return-multiple-rows-in-excel/

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