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.

One comment on “VLOOKUP going to next larger number”

Ah yes, the enigmatic VLOOKUP. Probably one of the most misunderstood of Excel’s arsenal of functions. The VLOOKUP uses the Price Is Right’s “closest without going over” search methodology when the RANGE LOOKUP (that last parameter) is set to TRUE.

The actual, more technical answer, is that Excel (and subsequently DriveWorks) looks at the value and says, “Is this value larger than the one that I am looking for?” If the answer is no, then it holds onto that value as a possible winner, and moves on. As soon as Excel finds a value greater than the search value, it stops. This means that it is CRITICAL when using VLOOKUP with a TRUE range lookup, to have your values in ascending order, whether they be numeric or text.

There are a few ways to work with this (rather than around it). The easiest is to change your perspective. Think of the lookup table as saying “Anything equal to or greater than this value should yield…” In IF logic, it changes the direction of your nests:

In this case, if X is greater than or equal to 10, then it will keep going to check the next highest value. If it isn't, then it will dump out of the IFs and return your answer.

So how handle the case where anything smaller than 0.25 to yield the first answer? Simply shift your rows down one and add a zero value. If the value that Excel/DriveWorks is comparing to first is a zero, odds are that it's going to pass that one and look at the first value. (assuming positive numbers)

So in this pipe case, if you wanted to check DWVariableCouplerDiameter to fit inside of the pipe, you could use the rule:

If we look at a value of, for example, 1.25, it would go down the chart starting at 0. DWVariableCouplerDiameter is greater than 0, so 1 inch will work. It will then check 1.05. DWVariableCouplerDiameter is greater than that, so 1-1/4 inch is a viable (and better) option and move on. 1.38 is greater than our search value, so it will just stop there and take the last valid answer, which is the 1-1/4 inch pipe. If we try a ridiculously large value like 249, we will fall off the end of the chart, so Excel/DriveWorks will take the last value, which we have conveniently labeled, "No Available Pipe."

OK, FINE! Well what if you need it to interpolate? Yeah, there's an app for that. (ok, so we wrote a user-defined function, not an app, but "yeah, there's a user-defined function for that" really doesn't sound as catchy) 🙂