Make each cell in a column update from the same row of another sheet?

Question

I'm looking for a function I could apply to a column that would make each cell in the column check the value of the first cell in its row and the first cell in its column and then return a value from the same cell of another sheet (matching first-cell-of-row + first-cell-of-column values).

For instance:

• In sheet #1 the value of cell "B5" is "purple"; the value of the first cell in column "B" is "red"; the value of the first cell in row "5" is "blue".
• In sheet #2, the value of the first cell in column "B" is "red"; the value of the first cell in row "3" is blue"; I'd like a function to make cell "B3" return the value "purple" automatically—except I'd like to apply this function to the entire column

Is this possible? If you have any questions about my example or my question, please ask and I'll try to clarify. Thanks!

Answer 1

You can use index() and match(), along with absolute references for this. I don't know the details of the first row and column in either sheet so I assume that they will be populated by you. You can then put a formula like the following starting from B2 on sheet #2:

=iferror( index(
    'Sheet 1'!$A:$N,
    match($A2, 'Sheet 1'!$A:$A, 0),
    match(B$1, 'Sheet 1'!$1:$1, 0)
))

This formula simply pull the cell from 'Sheet 1'!$A:$N with matching first row and column. You will need to update the range accordingly if your data on sheet 1 goes beyond column N.

In the row matching function, you want to make the column references absolute so that you will always be comparing the first column on each row when you drag the formula across horizontally. Likewise, in the column matching function, you want to make the row references absolute so that you will always be comparing the first row on each column when you drag the formula down.