Return Nth Result on a Multiple Criteria Index Match

  • Hi All,


    I hope one of you experts out there can help me. My current formula is this:


    {=INDEX(LOCSPC!N:N,MATCH(1,(LOCSPC!T:T=BINTRAN!AE2)*(LOCSPC!C:C>=BINTRAN!Z2)*(LOCSPC!C:C<=BINTRAN!AA2)*(LOCSPC!D:D<BINTRAN!X2)*(LOCSPC!I:I>=BINTRAN!AC2),0))}


    Or to simplify:


    {=INDEX(Array,MATCH(1,(Criteria 1)*(Criteria 2)*(Criteria 3)*(Criteria 4)*(Criteria 5),0))}


    Right now the formula returns the first value that matches the criteria from a sorted array. Basically I'd like to adjust this formula to return the Nth result i.e. the 2nd, 3rd, 4th, etc. result.


    I looked up the formula below online which is fine for a single criteria but I need it for multiple criteria and I'm not sure how to adjust. Any help would be greatly appreciated!!!


    {=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Just incorporate your criteria in multiple IF statements:


    =INDEX(Array,SMALL(IF(Range1=Criteria1,IF(Range2=Criteria2,IF(Range3=Criteria3,IF(Range4=Criteria4,IF(Range5=Criteria5,ROW(Array)-ROW(INDEX(Array,1))+1))))),nth))


    Regards

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Hey XOR LX


    This is really useful, thanks for posting - i have spent 2 days trying to get this to work.
    Could i ask how can i modify this formula so that if any of the criteria is blank it searches any of the other variables not blank?

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Not sure what you mean there, sorry.


    Can you clarify with a small, mocked-up example plus expected result?


    Regards

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Apologies, take your formula above as an example


    =INDEX(Array,SMALL(IF(Range1=Criteria1,IF(Range2=Criteria2,IF(Range3=Criteria3,ROW(Array)-ROW(INDEX(Array,1))+1))),nth))


    I have 3 data lists that i am using to specify the criteria in the Ranges. If Range1 is blank and the user selects criteria for Range2 & Range3, is there anyway to display the output? At the moment if any of the criteria is missing, im getting a #num error

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Still not sure what you mean precisely.


    What do you mean by "If Range1 is blank"? That all the entries within that range are blank?


    And what do you mean by "if any of the criteria is missing"? You mean that one or more of the criteria is blank? Or simply not there (if so, what does that even mean?)?


    This is why I suggested posting a small, mocked-up dataset with expected result...


    Regards

  • Re: Return Nth Result on a Multiple Criteria Index Match


    =INDEX(Array,SMALL(IF(Range1=Criteria1,IF(Range2=[I]Criteria2,IF([I]Range3=[I]Criteria3,ROW([I]Array)-ROW(INDEX([I]Array,1))+1))),[I]nth))
    [/I][/I][/I][/I][/I][/I]
    using the example formula above, and you take the example1 criteria below and insert them in the above formula:


    example1
    Criteria1 = BLANK (NO VALUE EXISTS IN THE REFERENCE CELL)

    [I]Criteria2 = BLUE
    [I][I]Criteria3 = RED


    [/I][/I][/I][I][I][I][I]expected outcome is that all records in table with Blue & Red listed will display in an index list.[/I][/I][/I][/I][I][I][/I][/I]
    Based on the current formula all criteria must be present, if one criteria is not specified (as there is no value in the cell to look search for) it returns a #NUM error.
    I'm trying to construct a formula that will return the index based on any combination of the criteria above, even if its blank.


    example2
    Criteria1 = GREEN
    [I]Criteria2 = BLANK [/I](NO VALUE EXISTS IN THE REFERENCE CELL)[I]
    [I][I]Criteria3 = BLANK
    [/I][/I][/I](NO VALUE EXISTS IN THE REFERENCE CELL)


    [I][I][I]expected outcome is that all records in table with Green listed will display in an index list.


    [/I][/I][/I]Is there a way to write the formula above that it will return a value if any of the criteria is met and if the Criteria2 & 3 reference cell is updated the formula will dynamically filter out the data based on the new data?

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Ah, thanks for the clear explanation. I see what you mean now.


    =INDEX(Array,SMALL(IF(IF(Criteria1="",1,Range1=Criteria1),IF(IF(Criteria2="",1,Range2=Criteria2),IF(IF(Criteria3="",1,Range3=Criteria3),ROW(Array)-ROW(INDEX(Array,1))+1))),nth))


    Regards

  • Re: Return Nth Result on a Multiple Criteria Index Match


    Thank you so much, i have been struggling with this for 2 days
    You. Are. A. LEGEND!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!