# Returning Values to the Left of a VLOOKUP by Allen Wyatt
(last updated January 12, 2019)

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.

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.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Storing AutoText Entries with a Document

AutoText entries can provide quite a bit of flexibility and power in a document. If you want to share those entries with ...

Discover More

Entering Formulas in Excel

The way you signify that you are entering a formula is to start a cell entry with an equal sign. Here is the reason why ...

Discover More

Adjusting the Order of Items in a Chart Legend

When charting your data, a legend is always a nice finishing touch. You may want to change the order in which items ...

Discover More Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

##### More ExcelTips (ribbon)

EOMONTH Function is Flakey

Some users have reported problems using the EOMONTH function in later versions of Excel, beginning with Excel 2007. The ...

Discover More

Modifying Proper Capitalization

The PROPER worksheet function is used to change the case of text so that the first letter of each word is capitalized. If ...

Discover More

Counting with Formulas

When you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip ...

Discover More
##### Subscribe

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 0?

2019-01-15 10:21:04

J. Woolley

@Gary H: For your interest, here is a complete discussion of the CHOOSE function including VLOOKUP/CHOOSE: https://www.ablebits.com/office-addins-blog/2018/05/30/choose-function-excel-formula-examples/

2019-01-14 11:06:50

Gary H

The real take-away here is the use of the CHOOSE function in the formula:
=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

You can use the Lookup and "you don’t need to sort " the data first.

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

I proposed the following but it was not included in the Tip above:

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

What about the LOOKUP function? It may be outdated, but the Vector form (see Excel Help) has a very simple and understandable formula and works quite happily - as long as the search column is sorted into alphabetical/numerical order.

##### This Site

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.