# 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,IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

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

try to uoload your sample file and i'll will show it to you

regards

• 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!

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

You're welcome!

Cheers

## Participate now!

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