Popular Topics

# 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.