Search code examples
excelexcel-formulavlookup

Using tables and column titles in VLOOKUP

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!!

Solution

  • This should work for you:

    =VLOOKUP(E31,tblCosts[#All],COLUMN(tblCosts[Jun-17]),false)

    The COLUMN(TblCosts[Jun-17]) returns the column number of the field you want.

    The VLookup searches the first column of tblCosts[#All] - (all the data in the table) for the data (in E31) and returns the data in the column you want.

    This formula will continue to work even if you add columns or rows to the data.

    Make sure that any heading which looks like a date in your spreadsheet eg "Jun-17" is stored as Text and not as a Date, as it will not match the string "Jun-17" when you use VLookup.