Vlookup using column headers instead of column number


New Member
Oct 4, 2011
I am running into a issue with Vlookup where I have a list of Primary Asset IDs that I need to locate on a second sheet and return the Effective Date. The problem I am running into is that the second sheet is from an export from one of our programs and the column headers may be in a different order depending on the settings of each individual user. Below is the current formula I have right now.

=VLOOKUP("PL"&RIGHT(B2,6),'FOF TS'!A:BC,MATCH("Original Price Date",'FOF TS'!A1:BC1,0),FALSE)

I have the Match function to pull from the correct date column, but I don't know to get the Lookup array to pull from the Primary Asset ID column. This formula works if the Primary Asset ID is in column A but this wont be the case with every user. I have tried a few different versions of Vlookup and Match, and Index and Match but still come up with #N/A. Can anyone tell me what I am missing?


Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
vlookup can only do the lookup by the first column in the range you give it... so you may have to give up on using "vlookup", and use "match" and "index" instead.
Upvote 0
Some sample data if it helps. It involves client data so I am limited in how much I can paste. The first group of data is what I am trying to look up, with the formula inserted in the Date column. The second group of data is the lookup range, but the order of the columns can be changed depending on how it is exported by the user. The first part of my formula "PL"&Right(B2,6) is done to transform the ID to what is recognized on the second sheet. Thanks in advance to anyone that can help.

Client Name

<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"><colgroup><col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;"><colgroup><col width="64" style="width: 48pt;"><colgroup><col width="25" style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;"><colgroup><col width="64" style="width: 48pt;"><tbody>

Best Price Status Primary Asset ID Type Primary Asset ID Original Price Date
PASS CLIENT ID PL00070A 9/16/2014
PASS CLIENT ID PL00070B 9/16/2014
PASS INTERNAL PL00070C 9/16/2014
PASS CLIENT ID PL00070D 9/16/2014
PASS CLIENT ID PL00070E 9/16/2014
PASS CLIENT ID PL00070F 9/16/2014
PASS CLIENT ID PL00070G 9/16/2014
PASS CLIENT ID PL00070H 9/16/2014
PASS CLIENT ID PL00070I 9/16/2014
PASS CLIENT ID PL00071J 9/16/2014

<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"><colgroup><col width="18" style="width: 14pt; mso-width-source: userset; mso-width-alt: 658;"><colgroup><col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"><colgroup><col width="25" style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;"><colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"><colgroup><col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"><colgroup><col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"><tbody>
Upvote 0
You could use this. It works I moved the columns around. The results you see are actual, not just typed in. I don't know how efficient it is, but it's all I can come up with right now.

Excel 2010
1Primary Asset ID TypeBest Price StatusPrimary Asset IDOriginal Price DateClient NameIDDate
2CLIENT IDPASSPL00070A9/21/2014XXXXXXXXX00070A9/21/2014
3CLIENT IDPASSPL00070B9/25/2014XXXXXXXXX00070B9/25/2014
5CLIENT IDPASSPL00070D4/24/2014XXXXXXXXX00070D4/24/2014
6CLIENT IDPASSPL00070E3/18/2014XXXXXXXXX00070E3/18/2014
7CLIENT IDPASSPL00070F10/12/2014XXXXXXXXX00070F10/12/2014
8CLIENT IDPASSPL00070G7/1/2014XXXXXXXXX00070G7/1/2014
9CLIENT IDPASSPL00070H8/24/2014XXXXXXXXX00070H8/24/2014
10CLIENT IDPASSPL00070I6/4/2014XXXXXXXXX00070I6/4/2014
11CLIENT IDPASSPL00071J1/25/2014XXXXXXXXX00071J1/25/2014
Cell Formulas
N2=INDEX($A$1:$G$11,MATCH("PL" & RIGHT($M2,6),OFFSET($A$1,0,MATCH("Primary Asset ID",$A$1:$H$1,0)-1,COUNTA(A:A),1),0),MATCH("Original Price Date",$A$1:$H$1,0))
Last edited:
Upvote 0
Let Sheet1, A:C, house the processing.

Let Sheet2, A:G, house the data table with Primary Asset ID in column E and Original Price Date in G.

Note. It's not a good idea to work with question marks. Better to put up the expected results instead and state explicitly what they are. Also, why do you have a single date in column G, even if this is reality, while such is not helpful to the helper. That said:

In C2, Sheet1, enter and copy down:
Rich (BB code):

Or, select the area A:G of Sheet2 and name the selection as LTable including the headers. Then invoke:
Rich (BB code):
  INDEX(LTable,0,MATCH("Primary Asset ID",INDEX(LTable,1,0),0)),
  INDEX(LTable,0,MATCH("Original Price Date",INDEX(Ltable,1,0),0)))
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back