Lookup Formula Based on Header Title Not Column Number

Hello,

I am struggling with finding a formula that works.

I have 2 sheets of data. I need to lookup something on the 2nd sheet based on value of cell "A2" from the 1st sheet which is Material Number. The thing is - the data on the 2nd sheet always changes and the column numbers aren't always in the same order, so I cannot use a simple "vLookup" formula. I need a vlookup-style formula that is based on the title in the headers, because the titles always stay the same, instead of being based on the column number.

Is this possible?

See attached workbook, where I tried to incorporate the "match" function into my vlookup, but to no avail.

Thanks

Change your formula in C2 of Sheet1 to this:

=VLOOKUP(A2,Sheet2!A:T,MATCH("AGB1-",Sheet2!A$1:T$1,0),0)

Hope this helps.

Pete

Thank you both!!