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

	A	B	C	D
1	Check	Weight	Color	Material
2	=vlookup(OR("Material","Materials","Mat.","Mats")2,Sheet1!A3:C52,3,false)	5	Blue	Wood
3	=vlookup(OR("Material","Materials","Mat.","Mats")3,Sheet1!A3:C52,3,false)	6	Red	Rubber

<tbody>
</tbody>

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

-----

	A	B	C	D
1	Check	Weight	Color	Mats
2	=vlookup(OR("Material","Materials","Mat.","Mats")2,Sheet1!A3:C52,3,false)	5	Blue	Wood
3	=vlookup(OR("Material","Materials","Mat.","Mats")3,Sheet1!A3:C52,3,false)	6	Red	Rubber

<tbody>
</tbody>

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

-----

	A	B	C	D
1	Check	Color	Mat.	Weight
2	=vlookup(OR("Material","Materials","Mat.","Mats")2,Sheet1!A3:C52,3,false)	Blue	Wood	5
3	=vlookup(OR("Material","Materials","Mat.","Mats")3,Sheet1!A3:C52,3,false)	Red	Rubber	6

<tbody>
</tbody>

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!