Return Cell Address from Index Match

[silverbluemoon]

I have two tables on the same page; one is underneath the other. They are separate tables with separate headers. I am looking to find a value I computed in the Table A (the table on top) from Table B (the table below) and compare it to a new value computed in Table B and do something based on how the values relate to each other.

I figured the best way to do this was to first make sure the value in Table B exists in Table A. Table A exists at A59:E108; TABLE B at A115:164. The values to find are all in the B column, starting at the top of the in TABLE B. Easy Enough.

=IF(COUNTIF($B$59:$B$108,$B115)>0,"FANTASTIC","OOPS")

Ok, great, it exists. Now I look at the

ADDRESS( )

function and see that to return a cell address, I need a row and a column. Normally, I would use the match function here like this (the value I want is in Column E, so 5):

=ADDRESS(MATCH($B115,$B$59:$B$115,0),5)

The problem with this is that this only works is the MATCH returns the row of the cell in the SHEET, not of the row in the selected array. In this case, they are NOT one in the same, since there are two tables stacked on the sheet.

Long story short, I need to return the cell address of the lookup value of this:

INDEX($E$59:$E108,MATCH($B115,$B$59:$B$115,0))

But I can't seem to get there. Any advice? I wish I could just say:

ADDRESS(INDEX($E$59:$E108,MATCH($B115,$B$59:$B$115,0)))

But of course that doesn't work because ADDRESS( ) requires a row and column, separately. I just basically want to the return the cell address the lookup value is found in instead of the value itself.

Any help is much appreciated! I've been trying to wrap my head around this and trying various ways, only to realize, as stated above, why they don't work in my situation. Unfortunately, moving the tables to separate sheets at this point is not an option. Ideally, I would combine the "Does it Exist" COUNTIF( ) I started off with into an IF ( ) statement, just to cover my bases and rule out an easy error.

Thanks in advance!

 

[Aladin Akyurek]

Let's try:

=CELL("address",INDEX($E$59:$E108,MATCH($B115,$B$59:$B$115,0)))

 

[tusharm]

There are very, very few instances where knowing the address is necessary. So, my first question would be: what do you really want to accomplish?

If you really really really must use the ADDRESS function and you want the row number in the worksheet (rather than in the table as returned by MATCH), use

row(1st cell in table) + result of MATCH() -1

Also, if you have tables as Excel understands them (select any cell in the range of data, then select Insert tab | Tables group | Table button), you can use structured references (Excel will create them when you select all the data in a column in a table). I strongly recommend this approach. There are some instances where structured references are inadequate but those are few and far between.

I have two tables on the same page; one is underneath the other. They are separate tables with separate headers. I am looking to find a value I computed in the Table A (the table on top) from Table B (the table below) and compare it to a new value computed in Table B and do something based on how the values relate to each other.

I figured the best way to do this was to first make sure the value in Table B exists in Table A. Table A exists at A59:E108; TABLE B at A115:164. The values to find are all in the B column, starting at the top of the in TABLE B. Easy Enough.

=IF(COUNTIF($B$59:$B$108,$B115)>0,"FANTASTIC","OOPS")

Ok, great, it exists. Now I look at the

ADDRESS( )

function and see that to return a cell address, I need a row and a column. Normally, I would use the match function here like this (the value I want is in Column E, so 5):

=ADDRESS(MATCH($B115,$B$59:$B$115,0),5)

The problem with this is that this only works is the MATCH returns the row of the cell in the SHEET, not of the row in the selected array. In this case, they are NOT one in the same, since there are two tables stacked on the sheet.

Long story short, I need to return the cell address of the lookup value of this:

INDEX($E$59:$E108,MATCH($B115,$B$59:$B$115,0))

But I can't seem to get there. Any advice? I wish I could just say:

ADDRESS(INDEX($E$59:$E108,MATCH($B115,$B$59:$B$115,0)))

But of course that doesn't work because ADDRESS( ) requires a row and column, separately. I just basically want to the return the cell address the lookup value is found in instead of the value itself.

Any help is much appreciated! I've been trying to wrap my head around this and trying various ways, only to realize, as stated above, why they don't work in my situation. Unfortunately, moving the tables to separate sheets at this point is not an option. Ideally, I would combine the "Does it Exist" COUNTIF( ) I started off with into an IF ( ) statement, just to cover my bases and rule out an easy error.

Thanks in advance!

 

[mouthpear]

Works Great for me, TY

 

[Aladin Akyurek]

Works Great for me, TY

You are welcome.