Reference cell from another worksheet if Column and Row headings Match

[s_parker]

Hello Excel Forum,

I'm a new member to the site. Thank you for considering my question.

I'd like to populate "baby worksheets" from a "master worksheet" as seen in the attached example. I'd like the population to be done by matching the Row and Column headings.

For example, in the contact worksheet, I'd like the empty cells to automatically update themselves from the master sheet based on the column titles (First Name, Last Name etc) and the Row heading (1, 2, 3, 4 etc.).

The master sheet will be changed frequently and I'll likely need to add columns as as I go (which is why I'd like population to match by column title). I'm looking for an elegant solution and would prefer a formula because I have no experience with Visual Basic or Macros.

~Thank you for the help

Example.xlsx

[Fotis1991]

In B2 of your contact sheet, put this. Copy down and across. Same way for the other sheet.

=IFERROR(INDEX('Master Sheet'!G$2:G$1000,MATCH($A2,'Master Sheet'!$A$2:$A$1000,0)),"")

[martindwilson]

use this same formula in b2 of the speciality and contact sheets
=INDEX('Master Sheet'!$A$1:$J$500,MATCH($A2,'Master Sheet'!$A$1:$A$500,0),MATCH(B$1,'Master Sheet'!$A$1:$J$1,0))
fill across and down

[Pete_UK]

Put this formula in B2 of the Speciality sheet:

=INDEX('Master Sheet'!$A:$J,MATCH($A2,'Master Sheet'!$A:$A,0),MATCH(B$1,Table1[#Headers],0))

It should automatically copy itself down, then you can copy it across. Copy it into B2 of the Contact sheet, and across.

Hope this helps.

Pete