How to match based on ranges using formulas?

Question

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

TheMaster · Accepted Answer · 2017-11-17 20:33:24Z

3

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.

answered Nov 17, 2017 at 20:33

TheMaster

3,27212 silver badges28 bronze badges

1

@|'-'| check out the sample sheet. I can't get your formula to work.
– jason
Nov 17, 2017 at 21:20
1

I've changed your formula a little and it works. going to accept your answer
– jason
Nov 17, 2017 at 21:25

Add a comment |

1 Answer 1