# Thread: Vlookup to the left... (Two double zero three)

1. ## Vlookup to the left... (Two double zero three)

Can vlookup be tweeked to collect a value to the left of the lookup value?
EG: =VLOOKUP(E1,A1:E1,3,FALSE)

2. ## Re: Vlookup to the left... (Two double zero three)

Nope - you have to use INDEX/MATCH:
<code>=INDEX(return_column,MATCH(lookup_val,lookup _col,0))</code>

3. ## Re: Vlookup to the left... (Two double zero three)

Thats perfect rory...
Tx

PS: Congrats on your Star Post!!

4. ## Re: Vlookup to the left... (Two double zero three)

Thank you kindly! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
One other thing to note about INDEX/MATCH:
If you're like me, you probably quite often need to pull data from a few columns in a table - e.g. for a given value, you need the data in columns 1-3, 5 and 11-13. If you use INDEX/MATCH instead, you can add a separate column with the MATCH part of the formula, then refer to this in the INDEX formula, so you only actually do the lookup to get the row number once, whereas with VLOOKUP you are performing the same lookup each time.
Also, with a VLOOKUP, if you reference a big table, the VLOOKUP is affected by any change within that table; the INDEX/MATCH version is only affected by changes within the 2 referenced columns.

You can probably tell which one I prefer! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

5. ## Re: Vlookup to the left... (Two double zero three)

I am starting to see the benefits myself. Thanx for sharing this info...it certainly does allow for greater flexibility. My biggest exposure to INDEX/MATCH has been when they are combined to create a 2-way lookup which is convenient for large data tables.

Cheers for the insight!

6. ## Re: Vlookup to the left... (Two double zero three)

Hi Rory

You are so right with advantages of having a separate matchRow column when you need data from multiple columns
I also prefer to use Index/Match

Sometimes, I need to use a matchCol as well.
Because of the variable nature of some data sheets, I end up using the results of my row and column 'matches' with the OFFSET function 'anchored' to \$A\$1 on a particular sheet.
(Unfortunately, you have to remember to subtract 1 from each result to get the data item you need)

I could probably use combinations of the INDIRECT and ADDRESS to get what I need.
I guess what I really would like is worksheet function that works something like the VBA equivalent CELLS(row,col)

zeddy

7. ## Re: Vlookup to the left... (Two double zero three)

To avoid the "subtract 1" problem, I set up the data with an extra column between row headers and data, and columns headers and data. Then it is automatically adjusted. Then I also include one extra row at bottom and column to the right in the Data name definition. That way if I add data by inserting rows or columns inside the blank rows/columns, all formulas automatically adjust.

I use this layout of data for just about every major project. It has saved considerable time, and formulas never have to be adjusted/changed.

8. ## Re: Vlookup to the left... (Two double zero three)

Rudi,

If you want to do interpolation (linear, spline, etc) at the same time in your lookup, try this http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm . It is very flexible because it uses separate X and Y lookup arrays that can be anywhere on the sheet,

Teunis

9. ## Re: Vlookup to the left... (Two double zero three)

Hey Teunis,

Going above and beyond I see......thanx for the info. It will come in handy when I need it! Good to know I can tweek it further...
Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•