Index Match (find the next highest value) + multiple criteria

[camilaarbigaus]

Hi all, I am trying to do an index match with multiple criteria, but not to return an exact value and I'm having trouble doing it.
Can anybody help?


The idea is the following: I need to find the payment value (column B) for occurence #55 in IL
If there was no "state" column, I would use the formula:

=INDEX(B:B,MATCH(55,C:C)+1)

But if I don't filter by state, it will return the value from FL (200) instead of 400

state	payment	number of occurences (cumulative)
IL	100	1
IL	200	3
IL	300	50
IL	400	140
IL	500	149
FL	100	1
FL	200	56

Any ideas on how to solve this?

Thank you so much!!

 

[JamesCanale]

Maybe this:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
1	state	payment	number of occurences (cumulative)
2	IL	100	1		55	IL
3	IL	200	3
4	IL	300	50		400
5	IL	400	140
6	IL	500	149
7	FL	150	1
8	FL	250	56
Sheet4

Cell Formulas
Range	Formula
E4	E4	=INDEX(B2:B8,1+MATCH(E2-0.1,IF(A2:A8=F2,C2:C8,""),1))

 

[camilaarbigaus]

Maybe this:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
1	state	payment	number of occurences (cumulative)
2	IL	100	1		55	IL
3	IL	200	3
4	IL	300	50		400
5	IL	400	140
6	IL	500	149
7	FL	150	1
8	FL	250	56
Sheet4

Cell Formulas
Range	Formula
E4	E4	=INDEX(B2:B8,1+MATCH(E2-0.1,IF(A2:A8=F2,C2:C8,""),1))

you are the best!!!! thank you!

 

[camilaarbigaus]

Maybe this:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
1	state	payment	number of occurences (cumulative)
2	IL	100	1		55	IL
3	IL	200	3
4	IL	300	50		400
5	IL	400	140
6	IL	500	149
7	FL	150	1
8	FL	250	56
Sheet4

Cell Formulas
Range	Formula
E4	E4	=INDEX(B2:B8,1+MATCH(E2-0.1,IF(A2:A8=F2,C2:C8,""),1))

Any idea how I could add a second criteria? (like color?)

 

[JamesCanale]

Adding a second criteria would work like this:
Add a column B with a color and
=INDEX(C2:C8,1+MATCH(F2-0.1,IF((A2:A8=G2)*(B2:B8=G1),D2:D8,""),1))

But the cumulative nature of the lookup is going to be buggy with the match. It doesn't really work anymore. But this is how to have multiple criteria. Excel 365 would make this easy.