Match with interval

Question

I have this table

I have to get the index of column which has the interval that contains 165 (Tier 2). For example

165 -> Tier 2

15 -> Tier 0

...

I tried to split the text interval in MIN and MAX

MIN: =LEFT(B3; FIND("-";B3)-1)

MAX: =IF(RIGHT(B3;LEN(B3)-FIND("-";B3))="";1E+99;RIGHT(B3;LEN(B3)-FIND("-";B3)))

But now I don't know how to use the Match() function. I can't change the structure of the table. I can only use a formula.

Answer 1

Use a formula like below:

=INDEX($A$1:$C$1,MATCH(165,INDEX((--LEFT($A$2:$C$2,FIND("-",$A$2:$C$2)-1)),)))

With your local settings, change the , to ;:

=INDEX($A$1:$C$1;MATCH(165;INDEX((--LEFT($A$2:$C$2;FIND("-";$A$2:$C$2)-1));)))

The inner INDEX returns an array of the first numbers: 1,20,150. The Match then returns where the value 165 lands in that array and returns the relative column number to the INDEX.