VLOOKUP going to next larger number

EmailFacebookGoogle+LinkedInTwitterShare

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.