Using Index Match -1 Match Type when matching 2 values

[sdwkelly]

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 example is ...

I have 2 inputs: 

- A = 32%

- B = 432

I have a table I would like to pull from that looks like this:

Percentage | Value | Type

30%            | 400    | red

30%            | 500    | blue

30%            | 600    | green

40%            | 400    | pink

40%            | 500    | orange

40%            | 600    | black

I want the array to be able to read both the percentage and value as ranges based on my inputs because I'm not entering exact numbers into the formula. So 32% should return the array of 30%, and 432 should return the array of 400, so the answer would be red.

[#2]

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.

[#3]

@sdwkelly 

Where E1 is the percentage you're trying to find and F1 is the value, you could try:

=IFERROR(INDEX(Table2[Type], MATCH(1,(Table2[Percentage]=E1)*(Table2[Value]=F1), 0)), LOOKUP(2, 1/(Table2[Percentage]<=E1)/(Table2[Value]<=F1), Table2[Type]))