My need is to compare content cells from two different sheets in the same file and get the cell value from another column of the first one.

This is the tested formula to find the right position:

ADDRESS(MATCH(B;Sheet1::Table 1::$I);7;4;1;)

it gives, for example, G12 in a relative cell in the second sheet, but I need the value of "G12" cell from the first sheet.

How can I do this?


ADDRESS has an optional parameter specifying the table name for the reference which can span across sheets by specifying the sheet name before the table name.

ADDRESS(row, column, addr-type, addr-style, table)

table: An optional value specifying the name of the table. table is a string value. If the table is on another sheet, you must also include the name of the sheet. If omitted, table is assumed to be the current table on the current sheet (that is, the table where the ADDRESS function resides).

=ADDRESS(3, 3,,,"Sheet 2::Table 1") creates the address Sheet 2::Table 1::$C$3.

ADDRESS returns a cell address string which can be used to look up the value located in the referenced cell using INDIRECT(addr-string, addr-style).

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .