Combining ADDRESS with an INDEX MATCH formula, to find cell reference

[asd6231]

Hi there,

I currently have an INDEX MATCH formula which is working across 2 spreadsheets and returning the value of the cell I want it to, but I want it to return the reference of the cell instead of the value it contains. I keep getting different errors when I try to use the address function.

I think I'm closer to using it correctly now, but I'm getting the "There's a problem with the formula. Not trying to type a formula?" error message.

This is my INDEX MATCH

=INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0))

This works and is currently returning 'E' which is what is in the cell it's found. I want it to return $G13 which is the address of the cell with an absolute column.

This is as far as I've got putting it in the address formula

=ADDRESS(3,INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0)),3,0,'[otherspreadsheet]May '!)

I'm using 3 as my row number because this is the row number I've used in the INDEX formula, so it's the row that cell I want returning is in, but I'm somewhat unsure on this.

I'm not sure what the error is or how to resolve it, or even if ADDRESS is the correct formula to be using at this point.

Many thanks for any help with this!

 

[BlueHornet]

Have you tried a simple
=ADDRESS( MATCH( "value or cell reference", "column containing the value", FALSE), MATCH( "value or cell reference", "row containing the value", FALSE)) ?

I haven't tried that across workbooks, but within a worksheet it worked fine.

 

[RandomizerTX]

Hi, asd:

Is this what you're looking for? I think you need only the MATCH function to look up the column, and you don't need the INDEX function to return the cell's contents.

Sheet2 is the "other" spreadsheet you reference.

=ADDRESS(3,MATCH(C3,Sheet2!$E$11:$I$11,0),3)

Hi there,

I currently have an INDEX MATCH formula which is working across 2 spreadsheets and returning the value of the cell I want it to, but I want it to return the reference of the cell instead of the value it contains. I keep getting different errors when I try to use the address function.

I think I'm closer to using it correctly now, but I'm getting the "There's a problem with the formula. Not trying to type a formula?" error message.

This is my INDEX MATCH

=INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0))

This works and is currently returning 'E' which is what is in the cell it's found. I want it to return $G13 which is the address of the cell with an absolute column.

This is as far as I've got putting it in the address formula

=ADDRESS(3,INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0)),3,0,'[otherspreadsheet]May '!)

I'm using 3 as my row number because this is the row number I've used in the INDEX formula, so it's the row that cell I want returning is in, but I'm somewhat unsure on this.

I'm not sure what the error is or how to resolve it, or even if ADDRESS is the correct formula to be using at this point.

Many thanks for any help with this!

 

[asd6231]

Hi, asd:

Is this what you're looking for? I think you need only the MATCH function to look up the column, and you don't need the INDEX function to return the cell's contents.

Sheet2 is the "other" spreadsheet you reference.

=ADDRESS(3,MATCH(C3,Sheet2!$E$11:$I$11,0),3)

Hey,

Thanks this is helpful. But when I added it, it referenced the original spreadsheet so I got $C3, the cell I was matching. I then added the final part of the ADDRESS function [sheet_text] to reference the other spreadsheet, and got the first "There's a problem with this formula" message again. I wonder if the problem is how I'm adding the sheet text part.

I'm going to look more at just using MATCH as well.

 

[asd6231]

Hi, thanks for the reply, it is working within the workbook but it's throwing me the same "There's a problem with this formula" error when I try to reference across workbooks. I wonder if that's where the problem is creeping in for me. I'll also have a look at just using MATCH as suggested and see if that helps.

 

[asd6231]

Hi, thanks for the reply, it is working within the workbook but it's throwing me the same "There's a problem with this formula" error when I try to reference across workbooks. I wonder if that's where the problem is creeping in for me. I'll also have a look at just using MATCH as suggested and see if that helps.

Hi, thanks for the reply, it is working within the workbook but it's throwing me the same "There's a problem with this formula" error when I try to reference across workbooks. I wonder if that's where the problem is creeping in for me. I'll also have a look at just using MATCH as suggested and see if that helps.

 

[asd6231]

Hey just as an update, eventually using the CELL function, with "address" as the format worked. It doesn't offer all the options of the ADDRESS formula, like making different parts of the reference absolute, but in the end it turns out I didn't need to do this piece of work so I won't be following it up any further for now.

Here's the formula in the end:

=CELL("address",INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0)))

and it returned

'[otherspreadsheet]May '!$E$13

Many thanks