How to write an INDIRECT formula in R1C1 style to return a value from another sheet?

Hi!

I'm trying to get this formula right:

=INDIRECT("R"&MATCH(Sheet1!B1;Sheet2!C:C;0)&"C"&COLUMN(Sheet2!D:D);FALSE)

I've entered the formula in cell A1 of Sheet1 and it uses the R1C1 reference style of the INDIRECT function.

The MATCH function checks column C of Sheet2 for a match with B1 of Sheet1 and returns the row number of the first such match. The COLUMN function returns the column number of column D of Sheet2, which is 4.

Let's say B1 of Sheet1 matches with cell C1 of Sheet 2. The row number returned is then 1. The INDIRECT formula then becomes =INDIRECT("R"1&"C"4;FALSE), which returns the content of D1 - of Sheet1!

The big question is: How can this formula be written so that it returns D1 of Sheet2 instead of D1 of Sheet1?

I hope someone can help!

Best regards,
Marbleking

Please Login or Register  to view this content.

Why would you want to use a volitile formula when this would do the job?
In A1

Please Login or Register  to view this content.

or in R1C1 style

Please Login or Register  to view this content.

Originally Posted by Marbleking

Hi!

I'm trying to get this formula right:

=INDIRECT("R"&MATCH(Sheet1!B1;Sheet2!C:C;0)&"C"&COLUMN(Sheet2!D:D);FALSE)
The big question is: How can this formula be written so that it returns D1 of Sheet2 instead of D1 of Sheet1?
I hope someone can help!
Best regards,
Marbleking

Try this:

Please Login or Register  to view this content.