Mar 23 2022 10:29 AM
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.
Mar 23 2022 12:51 PM - edited Mar 23 2022 12:53 PM
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.
Mar 23 2022 01:41 PM
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]))