Sam uses the VLOOKUP function frequently. It's very handy, but there is one severe limitation—the lookup can reference only columns to the right. This means Sam cannot use, as the third parameter for VLOOKUP, a negative value to reference a column to the left. He wonders if there is a way around this limitation.

There are actually three ways around this limitation—restructuring, using INDEX, and using CHOOSE. I'll look at each of these methods, in turn.

*Restructuring Your Content*

This may be the least desirable approach, but I'll get it out of the way right up front. If you find that you need to return values to the left of your lookup quite often, you might consider restructuring your worksheet so that the values are to the right of your formula.

An alternative method of restructuring is to use a helper column to the right of your formula. This helper column simply needs to refer to the actual return values. For instance, if your return values are in column A and your formula is in column E, you might add a helper column a J. The formula in J1 would simply be =A1. Copy it down, and then use column J as your return values in the formulas in column E.

*Using INDEX and MATCH*

Perhaps the most common approach to the problem Sam is experiencing is to use a combination of the INDEX and MATCH functions instead of VLOOKUP. For instance, let's say you have the following VLOOKUP formula:

=VLOOKUP(G1,$C$1:$E$100,3,TRUE)

This looks up in the range C1:C100 the value in G1 (as an approximate match) and gets the corresponding value in E1:E100. This formula is equivalent to the following formula:

=INDEX($E$1:$E$100,MATCH(G1,$C$1:$C$100,1))

So, if you want to get a column to the Left of your lookup column (for example A1:A100), you could use something like:

=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,1))

If you want an exact match returned by the lookup, all you need to do is change the final 1 in the MATCH function to a 0, like this:

=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,0))

*Using VLOOKUP and CHOOSE*

If you actually want to continue using the VLOOKUP function in your formula, you can "fool" it into retrieving values to the left by also including the CHOOSE function.

To illustrate this, let's assume that your lookup values are in column D, and the return values are in column A. In cell G1 is your lookup value. The following formula will return the proper values:

=VLOOKUP(G1,CHOOSE({1,2},$D$1:$D$100,$A$1:$A$100),2,FALSE)

The CHOOSE function returns an array constituted of the cells indicated. The VLOOKUP function then returns a value from the second column of that array, which just happens to be column A, to the left of column D.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (13608) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

**Professional Development Guidance!** Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out *Professional Excel Development* today!

In the newest version of Excel, a change in how formulas are calculated can cause havoc for some "older" formulas. Here ...

Discover MoreWhen working with data taken from the real world, you often have to determine which certain conditions were met, such as ...

Discover MoreIndirect references can be very helpful in formulas, but getting your head around how they work can sometimes be ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2021-03-27 11:57:23

Roy

So the tip's formula that uses CHOOSE() adapted to using INDEX() would be: (data in, say, A1:E100, and lookup value in G1)

=VLOOKUP(G1, INDEX($A$1:$E$100,SEQUENCE(ROWS(A1:F5)), {4,1}), 2, false)

INDEX() returns an array of only the necessary columns, not the whole data table, with column 4 (D) first, then column 1 (A) second. So now your formula has a lookup array with the lookup column to the left of the result column and VLOOKUP() will function normally. Which in this case has the effect of "looking left."

MANY more people are good with using INDEX() than CHOOSE() (familiarity and comfort that one is doing things right), INDEX() has none of the flakiness with results that CHOOSE() can have (though to be fair, the flakiness happens only when giving it a several column array rather than single column arrays to do the choosing from, but then given a data table and lack of familiarity, that's just what a casual user of it would likely do (as long as one is being fair, eh?)), and creating dynamic ranges from other formulas' results in an INDEX() function is something done all the time, not so for CHOOSE().

More complex uses of VLOOKUP() can be accomodated as well, by adding columns in the INDEX() function. So for a VLOOKUP() desiring two results from the table (column A nd column E, say, output in that order) for a single lookup (G1) would use the following:

=VLOOKUP(G1, INDEX($A$1:$E$100,SEQUENCE(ROWS(A1:F5)),{4,1,5}), {2,3), false)

Note:

SEQUENCE(ROWS(A1:F5)) and ROW(1:5), either could be used, the latter being "old school" — but SEQUENCE() takes care of a couple flakiness issues with ROW() now that we have SPILL functionality.

Though it seems one should be able to use a zero or nothing at all for the row parameter, when passing an array constant like {4,1} to INDEX(), it requires the number of rows (or columns if passing an {array constant} to be explicitly specified. Even if one uses "{5}" instead of "5" to specify column 5, one must then also specify the rows explicitly. So that's a knock against the "familiarity" arguement as most people seem religious about being able to write something like "INDEX(array,,3)" rather than "INDEX(array,0,3)" and get pretty pathological about not having the two commas in a row. To be honest though, that's more a personal problem than a familiarity problem, so... (Which I honestly don't understand anyway: writing a "0" is just as "I'm a clever insider" or "cool-looking" as the two commas and has the advantage (for other users) of having a parameter in place, even if it is cryptic to the casual user but it's two commas or bust for the religious. Just strange.)

2019-01-15 10:21:04

J. Woolley

2019-01-14 11:06:50

Gary H

=VLOOKUP(G1,CHOOSE({1,2},$D$1:$D$100,$A$1:$A$100),2,FALSE)

CHOOSE is not an obvious function (at least to me) to use if you are not familiar with arrays and their usage.

The web page https://www.contextures.com/excelchoosefunction.html is one of the few help pages that gives the example of using arrays for the index_num of the CHOOSE function.

The benefit of reading ExcelTips, besides solving people’s Excel problems, is learning of new solutions you didn’t even realize were possible.

2019-01-12 17:34:36

Alex B

Not only that, the Lookup version is expandable to allow "multi-column" lookups.

You just need to be aware of the following form

=LOOKUP(2,1/($C$1:$C$100=G1),$A$1:$A$100)

Now say Column G is product name & Column H is product colour (with C & D being the equivalent reference columns), you can now expand your formula to look at both.

=LOOKUP(2,1/(($C$1:$C$100=G1)*($D$1:$D$100=H1)),$A$1:$A$100)

If you want to see how this works I initially got it from here where how it works is explained:

https://www.contextures.com/excelfunctionlookup.html?awt_l=KPwMX&awt_m=3awuejWAuYrB3hJ

(Contextures - LOOKUP Function Examples)

Note: This is NOT entered in as an array formula eventhough it might looks a lot like one.

2019-01-12 11:17:08

Yvan Loranger

oops, my previous entry should have ended thus:

B4:B7 D1:D4

4 j

8 k

2 m

5 x

2019-01-12 11:01:59

Yvan Loranger

A job for LOOKUP, assuming your col. 1 is sorted ascending. (& the 2 columns can start at diff. rows!)

Syntax: LOOKUP(search-value, search-vector, result-vector)

[the search-vector must be sorted ascending & the search value can use wildcards].

=LOOKUP("m",D1:D4,B4:B7) yields 2

B4:B7 D1:D4

4 j

8 k

2 m

5 x

2019-01-12 08:35:18

Brian Hershman

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2021 Sharon Parq Associates, Inc.

## Comments