Excel Formula Multiple Criteria with Moving Header Changing to Different Column

[tropics123]

Hi, just wondering if this is a possibility with the Index/Match formula. Or is there a better formula when I don't know the column position of the header? The new report we get now, the header name is not always in the same order so the index/match formula isn't cutting it anymore.

Example:
On our main sheet, I need to pull in Bob's date of birth. On the new report I get, the "Name" in Column A always stays the same but the date of birth could be in column b or the next time it's in column c, and so on. Basically the date of birth column could be a moving target.

Main Sheet

Name	Date of Birth
Bob	Need formula to look at Bob's name to the left and pull in his date of birth here (without knowing the "date of birth" column position on the report I received
Mary
Kristin
Joe

Report I receive

Column A	Column B	Column C	Column D
Name	Date of Birth	Date of Hire	Rate
Bob	1/1/1978	2/2/2015	$15
Mary	5/6/1980	11/20/2001	$20

 

[DRSteele]

Seems straightforward enough. Try this:

Book1
	A	B	C	D
1	Name	Date of Birth
2	Bob	1/1/1978
3	Mary	5/6/1980
4	Kristin	#N/A
5	Joe	#N/A
6
7	Name	Date of Birth	Date of Hire	Rate
8	Bob	1/1/1978	2/2/2015	$15
9	Mary	5/6/1980	11/20/2001	$20
Sheet51

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX($A$8:$D$9,MATCH($A2,$A$8:$A$9,0),MATCH(B$1,$A$7:$D$7,0))

 

[tropics123]

Worked perfectly!! Thank you DRSteele!!

 

[DRSteele]

Super! You're welcome.