How to use a table column name in VLOOKUP

[scecchin]

Hi

I would like to use a VLOOKUP referring to a table as follows;
=VLOOKUP($D$12,TABLE1,TABLE1[HOLIDAY],FALSE)
instead of
=VLOOKUP($D$12,TABLE1,2,FALSE)

I am not clear if this is doable without additional formulas, or with which formulas to use if more are needed.

Thanks
Sergio

 

[Mike H..]

Hi

I would like to use a VLOOKUP referring to a table as follows;
=VLOOKUP($D$12,TABLE1,TABLE1[HOLIDAY],FALSE)
instead of
=VLOOKUP($D$12,TABLE1,2,FALSE)

I am not clear if this is doable without additional formulas, or with which formulas to use if more are needed.

Thanks
Sergio

Hi,

You can't do it quite like that because you are using 2 columns in your standatd vlookup. Try it like this

=VLOOKUP(D12,Table1[[header 1]:[Holiday]],2,FALSE)

In my table 'Header1' was the header of the column to the left of the holiday column

 

[jeffreyweir]

Best you use and INDEX/MATCH combo. Something like:
=INDEX(TABLE1[HOLIDAY], MATCH($D$12,TABLE1[SomeLookupColumn],FALSE))

 

[NARAYANK991]

Hi Sergio ,

Why would you not want to use :

=VLOOKUP($D$12,TABLE1,2,FALSE)

Is it because you are not looking up your value in D12 in the first column of Table1 ?

Narayan

 

[scecchin]

Hi

Perhaps a little confusion here. I do want to look up $D$12 in the first column of the table and I want to retrieve the related value from the column [Holiday]. While this column is the second column in the table, if I enter the number 2, and then later insert a column, it breaks the formula. I want to reference the column name so I know what I am always getting. In a larger table, that would be helpful

I do have the formula working with MATCH, which allows the table to grow or contract without compromising the VLOOKUP formula, but I was curious if there was an even easier way to reference the column.

=VLOOKUP(D12,Table1,MATCH("Holiday",Table1[#Headers],0),FALSE)

(I am using Excel 2010)

 

[Mike H..]

Hi

Perhaps a little confusion here. I do want to look up $D$12 in the first column of the table and I want to retrieve the related value from the column [Holiday]. While this column is the second column in the table, if I enter the number 2, and then later insert a column, it breaks the formula. I want to reference the column name so I know what I am always getting. In a larger table, that would be helpful

I do have the formula working with MATCH, which allows the table to grow or contract without compromising the VLOOKUP formula, but I was curious if there was an even easier way to reference the column.

=VLOOKUP(D12,Table1,MATCH("Holiday",Table1[#Headers],0),FALSE)

(I am using Excel 2010)

The formula I gave you will do that and continue to do so even if you insert columns.

=VLOOKUP(I1,Table1[[Header 1]:[Holiday]],2,FALSE)

See the attached workbook. I changed the cell with the lookup value to I1 but that makes no difference.

 

[Mike H..]

Hi

Perhaps a little confusion here. I do want to look up $D$12 in the first column of the table and I want to retrieve the related value from the column [Holiday]. While this column is the second column in the table, if I enter the number 2, and then later insert a column, it breaks the formula. I want to reference the column name so I know what I am always getting. In a larger table, that would be helpful

I do have the formula working with MATCH, which allows the table to grow or contract without compromising the VLOOKUP formula, but I was curious if there was an even easier way to reference the column.

=VLOOKUP(D12,Table1,MATCH("Holiday",Table1[#Headers],0),FALSE)

(I am using Excel 2010)

Please ignore my previous post because it's a load of nonsense<g>

A much better formula that will work when you insert columns is this.

=INDEX(Table1[Holiday],MATCH(I1,Table1[Header 1],0))

See the attached workbook

 

[scecchin]

Thanks Mike, that works well, as well. This leaves me with a few options although each option still obliges multiple functions. Perhaps one day Microsoft with bring an enhancement where a formula like VLOOKUP would be smart enough to recognize the table column name and use the its column number without the additional formulas.

 

[Mike H..]

Thanks Mike, that works well, as well. This leaves me with a few options although each option still obliges multiple functions. Perhaps one day Microsoft with bring an enhancement where a formula like VLOOKUP would be smart enough to recognize the table column name and use the its column number without the additional formulas.

Hi,

I think that MS are unlikely to do that because it's already provided with these structured references. Structured references that refer to a single column will track that column when inserts are made.But you never know, they might.

 

[vincent]

Hi Mr. Mike,

I was trying solve a problem with different formulas. Could not succeed.
I am attaching a file.

All the substrate names marked in yellow appears as in "Master-basic" sheet

You will notice different material names in columns E, H, J & L and the corresponding Microns "Mic" in F, I, K & M columns.

First of all I want the microns to appear IN 'BUDGET' sheet against the corresponding substrate name matching "Item" and "LPO" number of "Budget" sheet and "Data Field" Sheet.

Secondly, you will notice "qty (MT)" in columns "Q,S,U & W" against Some of the substrates appearing in columns "P, R,T & V" of "Data Field" Sheet. Here again, I need the value i.e "qty (MT)" to reflect in the corresponding cells of "BUDGET" Sheet matching "Item" and "LPO" number of "Budget" sheet and "Data Field" Sheet.

I am sure you will be able to help me.

Cheers!

Vincent