Using tables and column titles in VLOOKUP

user_4876561 0
Viewed 11224

I'm having a very difficult time with transitioning my VLOOKUP statement from simply referencing sheets and ranges (which work like a charm), to instead using table and column names.

I'm trying to make the VLOOKUP a bit more robust as the sheet where it is pulling from will constantly be changing data, so column numbers will change frequently. Thus, I'd like to just reference a column by its name.

  1. I have converted the source data sheet to a table.
  2. I have named all columns appropriately and double checked spelling.

This VLOOKUP works great (currently):

=VLOOKUP(E6,'Costs'!$A$2:$AE$84,19,FALSE)

However, what I would like to do is make it look this:

=VLOOKUP(E6,tblCosts[Order Number],tblCosts[June 2017], FALSE)

I have been fiddling with also trying to use MATCH which is not working either:

=VLOOKUP(E31,tblCosts[Order Number],MATCH(F4,tblCosts[June 2017],FALSE),FALSE)

UPDATE

This formula now works but it is returning the order number...not the cost for the month.

=VLOOKUP(E31,(tblCosts[Order Number]),(tblCosts[June 2017]),FALSE)

Normally I would keep trying this myself...but I am feeling overwhelmed and have been trying for hours. Any advice would be great.

Thank you!!

vlookup excel-formula excel
2 Answers
=VLOOKUP(E37,tblCosts[#All],match("Jun-17", tblCosts[#Headers]),FALSE)

This match is looking for the column name in all column headers and will return the number of columns to the right of the first column rather than the column number in the sheet. This avoids having to use COLUMN(tblCosts[Jun-17]) - COLUMN(tblCosts[Order Number]) + 1 when your table doesn't start in column A.

Ref: https://www.excel-university.com/vlookup-hack-4-column-labels/

user_89031220
Related