3

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!

  • What's the relation between B5 in sheet #1 and B3 in sheet #2? – Argyll Mar 4 '18 at 3:08
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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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