Blog VLOOKUP going to next larger number
This week I have working on a rather interesting DriveWorks project where I had to select the proper size of schedule 40 pipe that a given shaft coupler could just fit inside. As an example, if I had a coupler that has an 1.4″ outside diameter, I would choose the 1-1/2″ size pipe. (From the first table below.)
The problem is that if I use VLOOKUP to search the table, VLOOKUP returns the next size smaller from the table. Thus I would get the 1-1/4″ pipe – too small for my 1.4″ coupler.
|
Pipe Size |
Outside Diameter |
Inside Diameter |
|
1 |
1.32 |
1.05 |
|
1 1/4 |
1.66 |
1.38 |
|
1 1/2 |
1.9 |
1.61 |
|
2 |
2.38 |
2.07 |
|
2 1/2 |
2.88 |
2.47 |
|
3 |
3.5 |
3.07 |
So I modified my table in this way:
|
Pipe Size |
Outside Diameter |
Inside Diameter |
|
3 |
3.5 |
-3.07 |
|
2 1/2 |
2.88 |
-2.47 |
|
2 |
2.38 |
-2.07 |
|
1 1/2 |
1.9 |
-1.61 |
|
1 1/4 |
1.66 |
-1.38 |
|
1 |
1.32 |
-1.05 |
Now if I ask VLOOKUP to find the pipe size (searching for -1.4″), it returns the proper size because -1.61 is the next size smaller.
I had to reorder the records in the table because VLOOKUP wants its searching values in ascending order.
Certainly if I wanted to get the actual inside diameter, I need to use the ABS function to return me back to positive values.