Forum Discussion

sdwkelly's avatar
sdwkelly
Copper Contributor
Mar 23, 2022

Using Index Match -1 Match Type when matching 2 values

Hello,   I am trying to use index match as an array where I take into account 2 values in my match statement to determine which value to pull. However, I want my match type to be -1 for both. My ex...
excel
mtarler's avatar
mtarler
Silver Contributor
Mar 23, 2022

you can't do that directly, in fact you can't (reliably) use the -1 parameter on that Value column as it is because it isn't sorted. Also I think you mean the MATCH should be +1 as in less than or =
so you can do this:
=FILTER(TableA[Type],(TableA[Percentage]=INDEX(SORT(TableA[Percentage],,1),MATCH(A,SORT(TableA[Percentage],,1),1)))*(TableA[Value]=INDEX(SORT(TableA[Value],,1),MATCH(B,SORT(TableA[Value],,1),1))),"none")
and might need/want to add an IFERROR clause too
but basically I used that INDEX(SORT(), MATCH( A, SORT) ) to FIND that closest value and then used the FILTER to return the value(s) that match (note this can theoretically return multiple values if more than 1 match the conditions)

BTW if you really do want the -1 (= or next larger) then sub -1 instead of 1 for each SORT and MATCH

ALSO if you didn't realize the A and the B in the MATCH point at the percent and value cells to be found.

Resources