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))")

INDEX MATCH is the right approach, you just have to be careful in how you structure it.

Table A on the left. Table B on the right. We'll use [Customer Number] to look up [Post Code].

This is the formula to write in D2:

=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))

enter image description here

INDEX, as you know, returns the value of a cell at the intersection of a row and column. MATCH returns the relative position of a value in an array.

So for the two inputs to INDEX, first we find the row number in the source table that matches the lookup value we're using (Customer Number) (this is the traditional first half of an INDEX MATCH) by feeding MATCH a vertical array to look in, then we find the column number that matches the name of the column we're in by feeding MATCH a horizontal array comprising the header row of the source column.

You'll note that if you add a field to the source table, and change the header in the formula table, you get the new results without changing the formula.

enter image description here

..and that this formula copies across as well as down.

enter image description here

The two keys here are:

  • knowing that MATCH will count across as well as down.
  • manually changing the structured reference that Excel supplies you to the actual R1C1-style cell reference, such that you can make the column index of that cell reference dynamic rather than fixed (ie. C$1 as opposed to TableA[[#Headers],[Post Code]], which hard codes the value for the field you're in, and as such won't copy across, although it will work if you manually change the name of the column that has the lookup formula in it, and you only need to have one lookup field in the destination table).

NB I know this question is over three years old, but it's a good question, and a great demonstration of the versatility of the INDEX MATCH technique.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.