How to make vlookup criteria use column header

[chimneytop]

I have two sheets. Sheet1 has a table of data that I will need to lookup, and I will be pasting data from another workbook into Sheet2 that has somewhat consistent column headers but in different orders. I want to put a formula in Sheet2!A:A that finds a specific column in the pasted data and then uses it lookup a value in Sheet1. I've made three examples of Sheet2 below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Color[/TD]
[TD="align: center"]Material[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]=vlookup(OR("Material","Materials","Mat.","Mats")2,Sheet1!A3:C52,3,false)[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Wood[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]=vlookup(OR("Material","Materials","Mat.","Mats")3,Sheet1!A3:C52,3,false)[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Rubber[/TD]
[/TR]
</tbody>[/TABLE]

Goal for A2 is to return same result as =vlookup(D2,Sheet1!A3:C52,3,false)

-----

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Color[/TD]
[TD="align: center"]Mats[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]=vlookup(OR("Material","Materials","Mat.","Mats")2,Sheet1!A3:C52,3,false)[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Wood[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]=vlookup(OR("Material","Materials","Mat.","Mats")3,Sheet1!A3:C52,3,false)[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Rubber[/TD]
[/TR]
</tbody>[/TABLE]



Goal for A2 is to return same result as =vlookup(D2,Sheet1!A3:C52,3,false)

-----

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Color[/TD]
[TD="align: center"]Mat.[/TD]
[TD="align: center"]Weight[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]=vlookup(OR("Material","Materials","Mat.","Mats")2,Sheet1!A3:C52,3,false)[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Wood[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]=vlookup(OR("Material","Materials","Mat.","Mats")3,Sheet1!A3:C52,3,false)[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Rubber[/TD]
[TD="align: center"]6[/TD]
[/TR]
</tbody>[/TABLE]


Goal for A2 is to return same result as =vlookup(C2,Sheet1!A3:C52,3,false)

 

[t0azt]

=VLOOKUP(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Mat*",$1:$1,0),4),"1","")&ROW(A2)),Sheet1!$A$3:$C$52,3,FALSE)

This will work as long as the column label starts with Mat. Also it will take only the first instance of Mat, meaning that if you have Mat. in column C and Materials in column D it will use column C in the vlookup. Hope this helps!