Google-sheets – How to match based on ranges using formulas

google sheets

I have the following

           lower      upper
1          30
0.5        20         30
0          10         20
-0.5        0         10
-1                     0

Now, I have:

Mike  12
Tim   35
Fred  -5
Paul  8
Joe   28

I want to create a column C where a formula looks up the range puts in the correct number.

Mike  12   0
Tim   35   1
Fred  -5   -1
Paul  8    -0.5
Joe   28   0.5

How to do this?

I have created a sample sheet

https://docs.google.com/spreadsheets/d/1wOHSuZW4tLnjf7peo_lTufxJGJkr4KKS2Q0RmuoqLrA/edit#gid=0

Best Answer

Assuming the first table is in A1:C6, And the second table in A10:B17, C10: 

=INDEX($A$1:$A$6,MATCH(B10,$B$1:$B$6,-1))

Drag fill down.

Related Topic