Vlookup to the left… (Two double zero three)

Topic

New Reply

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

Reply | Quote

Replies

Nope – you have to use INDEX/MATCH:
=INDEX(return_column,MATCH(lookup_val,lookup_col,0))

Reply | Quote

Thats perfect rory…
Tx

PS: Congrats on your Star Post!!

Reply | Quote

Thank you kindly!
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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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%5B/url%5D . It is very flexible because it uses separate X and Y lookup arrays that can be anywhere on the sheet,

Teunis

Reply | Quote

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

Reply | Quote