Vlookup using column headers instead of column number

[bmackie97]

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?

Thanks

 

[skywriter]

It would be better if you could provide sample data.

 

[ztodd]

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.

 

[bmackie97]

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		ID		Date
XXXXXXX		XX00070A		?
XXXXXXX		XX00070B		?
XXXXXXX		XX00070C		?
XXXXXXX		XX00070D		?
XXXXXXX		XX00070E		?
XXXXXXX		XX00070F		?
XXXXXXX		XX00070G		?
XXXXXXX		XX00070H		?
XXXXXXX		XX00070I		?
XXXXXXX		XX00071J		?

<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>
</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>
</tbody>

 

[skywriter]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Primary Asset ID Type		Best Price Status		Primary Asset ID		Original Price Date				Client Name		ID	Date
2	CLIENT ID		PASS		PL00070A		9/21/2014				XXXXXXX		XX00070A	9/21/2014
3	CLIENT ID		PASS		PL00070B		9/25/2014				XXXXXXX		XX00070B	9/25/2014
4	INTERNAL		PASS		PL00070C		8/12/2014				XXXXXXX		XX00070C	8/12/2014
5	CLIENT ID		PASS		PL00070D		4/24/2014				XXXXXXX		XX00070D	4/24/2014
6	CLIENT ID		PASS		PL00070E		3/18/2014				XXXXXXX		XX00070E	3/18/2014
7	CLIENT ID		PASS		PL00070F		10/12/2014				XXXXXXX		XX00070F	10/12/2014
8	CLIENT ID		PASS		PL00070G		7/1/2014				XXXXXXX		XX00070G	7/1/2014
9	CLIENT ID		PASS		PL00070H		8/24/2014				XXXXXXX		XX00070H	8/24/2014
10	CLIENT ID		PASS		PL00070I		6/4/2014				XXXXXXX		XX00070I	6/4/2014
11	CLIENT ID		PASS		PL00071J		1/25/2014				XXXXXXX		XX00071J	1/25/2014
Sheet29

Cell Formulas
Range	Formula
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))

 

[Aladin Akyurek]

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:

=LOOKUP(9.99999999999999E+307,SEARCH(REPLACE(C2,1,2,"??"),Sheet2!$E$2:$E$11),Sheet2!$G$2:$G$11)

Or, select the area A:G of Sheet2 and name the selection as LTable including the headers. Then invoke:

=LOOKUP(9.99999999999999E+307,SEARCH(REPLACE(C2,1,2,"??"),
  INDEX(LTable,0,MATCH("Primary Asset ID",INDEX(LTable,1,0),0)),
  INDEX(LTable,0,MATCH("Original Price Date",INDEX(Ltable,1,0),0)))