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
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
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),)})
