google sheet : How to vlookup by matching value in between max and min?

Question

I have 2 sheets like this :

In that 2nd sheet, i want to lookup the id (S/M/L/XL) by checking if value is in between the Min value and Max value. So the goal is to have something like that 2nd sheet where in 'level' column, i need to put a formula so it will check the value is between what range so then it will retrieve the correct 'id' from 1st sheet.

the rule is if the value is >= minvalue and < max value

How can i do this ?

Thanks

Answer 1

use:

=INDEX(IF(A9:A="",,VLOOKUP(A9:A, {C2:C5, A2:A5}, 2, 1)))

Answer 2

Your first table, has overlapping values, so I suggest you think better about the rules you want to apply. For example, 1, according your table can match both "S" and "M" , same for 3, which can be "M" or "L". Once you have resolved that, you can use the QUERY function.

Example: =QUERY($A$2:$D$5, "select A,D where C<="&A2&" AND D >="&A2&" ORDER BY D DESC LIMIT 1 ")

Working solution can be found here: https://docs.google.com/spreadsheets/d/1oLVwQqihT_df2y_ZQnfx7By77HnKSFz0bcbOzMuWqOM/edit?usp=sharing

Answer 3

Rather than have min and max columns, you could just use one column to list incremental values that determine the next size, and use vlookup() with a sort option of true - this avoids overlapping values:

=arrayformula({"level";if(A2:A<>"",VLOOKUP(A2:A,{Source!C:C,Source!A:A},2,1),)})