INDEX MATCH based on 2 criteria and to return multiple values

Question

Hi all,&nbsp;&nbsp;I am having difficulty putting two logic together, that is &nbsp;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.&nbsp;&nbsp;This is where I have got....&nbsp;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....&nbsp;&nbsp;=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)))&nbsp;Any suggestions on what parts of the logic I am missing?&nbsp;Thank you!&nbsp;Sarah

sergeibaklan · Answer

Hi Sarah,&nbsp;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/&nbsp;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&nbsp;&nbsp;

Forum Discussion

INDEX MATCH based on 2 criteria and to return multiple values

1 Reply

Resources