Lookup with changing header columns

[tropics123]

Hi, any suggestions would be greatly appreciated. Below are two example files I receive from a vendor. The file is usually massive but for this purpose, I'm showing simple tables below.

The old formula I used was INDEX/MATCH to pull in Mary's phone to a different spreadsheet called MAIN. On the MAIN spreadsheet, there would be a designated cell and if I type in a person's name, then it would pull in different information into different cells, depending on what the formula is set-up to pull in. The "Phone" header used to move around to different columns. However, the new file we're receiving, the "Name" and "Phone" are moved again and in addition they added a bunch of columns in the front and most of the time they're blank, but we need to keep the columns there.

I tried using this VLOOKUP/MATCH formula (=VLOOKUP(lookup_value,range,MATCH(col_name,col_headers,0),0) but it doesn't work because the "Name" column needs to be the first column.

Does anyone have a solution if I want to lookup a person's name and their phone number (from the vendor sheet with columns are constantly shifting)? Please note that the person's name will change depending on what I type into cell B2 on my MAIN sheet.

Old File

Name	Address	Zip	Phone
Mary	123 Lake St	12356	###-###-####
Jane	9876 Welder Ave	23456	###-###-####
Joe	1234 Blaine Rd	45678	###-###-####

New File

ID	Other	Something	Something Else	Name	Address	Address 2	Zip	Phone
				Bob	8977 Cake Town		87765	###-###-####
				Mary	123 Lake St		12356	###-###-####
				John	5678 Blueline Rd		34456	###-###-####
				Larry	4876 Lola Ln		56789	###-###-####

 

[Snakehips]

@tropics123 I think you are needing similar to this solution that I provided a couple of days back.
Here
Using Index Match to find your appropriate header and then the desired name / telephone number from the column.
You will likely need a formula for each of your files, old and new.
Hope that helps.

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Other	Something	Something Else	Name	Address	Address 2	Zip	Phone			Name	Zip	Address
2					Bob	8977 Cake Town		87765	###-###-####			John	34456	5678 Blueline Rd
3					Mary	123 Lake St		12356	###-###-####			Bob	87765	8977 Cake Town
4					John	5678 Blueline Rd		34456	###-###-####
5					Larry	4876 Lola Ln		56789	###-###-####
6
List

Cell Formulas
Range	Formula
M2:N3	M2	=INDEX($A:$I,MATCH($L2,INDEX($A:$I,,MATCH($L$1,$A$1:$I$1,0)),0),MATCH(M$1,$A$1:$I$1,0))

 

[tropics123]

How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Other	Something	Something Else	Name	Address	Address 2	Zip	Phone			Name	Zip	Address
2					Bob	8977 Cake Town		87765	###-###-####			John	34456	5678 Blueline Rd
3					Mary	123 Lake St		12356	###-###-####			Bob	87765	8977 Cake Town
4					John	5678 Blueline Rd		34456	###-###-####
5					Larry	4876 Lola Ln		56789	###-###-####
6
List

Cell Formulas
Range	Formula
M2:N3	M2	=INDEX($A:$I,MATCH($L2,INDEX($A:$I,,MATCH($L$1,$A$1:$I$1,0)),0),MATCH(M$1,$A$1:$I$1,0))

Hi @Fluff
This is perfect! Thank you so much! Just curious, I noticed in your formula there are two commas before the second MATCH. Why is that?

=INDEX($A:$I,MATCH($L2,INDEX($A:$I,,MATCH($L$1,$A$1:$I$1,0)),0),MATCH(M$1,$A$1:$I$1,0))

 

[Fluff]

That matches the column header & because no row values has been used it returns all the rows for the column.