Index Match function with column & row lookups isn't working

[diversification]

Hello, I'm trying to create an INDEX MATCH function that will cross reference account numbers and return a date. Below I've created two mock Sheets of data similar to what I'll be using (Sheet_1 and Sheet_2.) The columns of data that do not matter are named "Whatever."

What we know:

1. The names of the columns we want to work with. On Sheet_1 it's "Dates" and "Account Numbers" and on Sheet_2 it's "Acct"
2. We know that the column headers will exist in Row 1 of their respective sheets

What we don't know:

1. The position of the columns we want. "Dates" could be in column A, R, Z, AN, ZZZ, etc, etc, etc. The same goes for "Account Numbers" and "Acct"

Obviously not knowing the column position is what makes writing this difficult, but I do need a formula that's generic so I can copy/paste it without needing much modification. I've tried to write in addition nested MATCH formulas which find the column locations and then use SUBSTITUTE to turn the column number into a letter, but it doesn't work out. Can anyone help me with this, please?


Sheet_1

Whatever	Dates	Whatever1	Account Numbers	Whatever2	Whatever3
blah	09/01/2020	blah	1234	blah	blah
blah	09/07/2020	blah	4444	blah	blah
blah	08/25/2020	blah	6622	blah	blah
blah	09/16/2020	blah	3030	blah	blah

Sheet_2

Whatever	Whatever4	Acct	Whatever5	Formula column
blah	blah	4343	blah
blah	blah	0011	blah
blah	blah	4444	blah	09/07/2020

Thanks!

 

[Fluff]

Easiest way is to convert both sets of data to structured tables
(the top table is called Table3)

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	Whatever	Dates	Whatever1	Account Numbers	Whatever2	Whatever3
2	blah	01/09/2020	blah	1234	blah	blah
3	blah	07/09/2020	blah	4343	blah	blah
4	blah	25/08/2020	blah	6622	blah	blah
5	blah	16/09/2020	blah	3030	blah	blah
6
7
8
9
10	Whatever	Whatever4	Acct	Whatever5	Formula column
11	blah	blah	4343	blah	07/09/2020
12	blah	blah	11	blah	#N/A
13	blah	blah	3030	blah	16/09/2020
14
Sheet1

Cell Formulas
Range	Formula
E11:E13	E11	=INDEX(Table3[Dates],MATCH([@Acct],Table3[Account Numbers],0))

 

[diversification]

This worked, thank you. I've never worked with Tables before, so I guess I'll need to start using them more frequently.

 

[Fluff]

You're welcome & thanks for the feedback.

Whilst tables do have their drawbacks, they can make life a lot easier.