I have a look up table below and I want o bin ranges. They are in columns A and B of an excel spreadsheet. I am using the formula =VLOOKUP(F2,$A$1:$B$11,2,1) to look up the range.
| Upper Edge | ROMI Range |
|---|---|
| 100 | 0 to 100 |
| 0 | 0.3 to 0 |
| 0.3 | 0.2 to 0.3 |
| 0.2 | 0.1 to 0.2 |
| 0.1 | 0 to 0.1 |
| 0 | -0.1 to 0 |
| -0.1 | -0.2 to -0.1 |
| -0.2 | -0.3 to -0.2 |
| -0.3 | -100 to -0.3 |
| -100 | -1000 to -100 |
I am getting the wrong results for the following.
| Values | Using Vlookup | Expected Range |
|---|---|---|
| 99 | -1000 to -100 | 0 to 100 |
| 0.25 | -1000 to -100 | 0.2 to 0.3 |
| -0.15 | #N/A | -0.2 to -0.1 |
Can you please let me know what I am doing wrong? Thanks.
CodePudding user response：
This won't work as you have overlapping ranges. 0 to 0.1 and 0 - 100. Also 0.3 to 0 is in the opposite direction.
Assuming you mean 0.3 to 0.4 and 0.4 to 100, then:
To get the lower edge:
=NUMBERVALUE(LEFT(C3,FIND(" ",C3,1)-1))
To get the lookup:
=VLOOKUP(F3,$A$3:$C$12,3,1)