I have two Excel tables, table A and table B. Table A contains the columns Customer ID, Postcode, Customer Name and several others that have the same column names as columns in table B. I want to create a formula, ideally using structured referencing so that the order of the columns in table B is irrelevant, that looks-up the value in table B that matches the Customer ID of the row I am on in table A and the column value of the column that I am in.
For example, if my formula is in the third column on table A and in a row with Customer ID "123", I want it to check its own column name (Customer Name) and lookup the value for Customer Name where the Customer ID = "123" in table B.
The following formula works fine for the Customer Name column:
=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))
but I want to be able to create a single formula that dynamically replaces the [Customer Name] piece with the name of the column that I am in so that I can just copy it across all of the columns. I have tried creating the reference using #Headers and using indirect but I get a Ref error:
=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")