Forum Discussion

Copper Contributor

Apr 01, 2019

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

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 instea...

excel

Formulas and Functions

AntsXL

Copper Contributor

Oct 29, 2020

Twifoo

Hi,

I found this subject interesting. But I have a twist on it.

I'm trying to use ADDRESS()&":"&ADDRESS() in a Match formula to define the lookup range but it doesn't work.

When I hit F9 on the address part it shows the expected complete range correct but with quotation marks around it, that seems to be the issue. Why?

=MATCH(LEFT(B2;7);"'Sheet1'!"&ADDRESS(E7+1;3;1;1;)&":"&ADDRESS(E7+1;50;1;1;);0)

in the formula above using F9 to see result "'Sheet1'!R4C3:R4C50"

When put separately in a cell:

="'Sheet1'!"&ADDRESS(E7+1;3;1;1;)&":"&ADDRESS(E7+1;50;1;1;) // Result 'Sheet1'!R4C3:R4C50

If I paste the latter result in the formula it works.

Thanks!

BidduthD

Copper Contributor

Sep 12, 2024

AntsXL

Dear Sir,

="'Attendance'!"&INDEX(ADDRESS(COLUMN(DATA)+10,ROW(DATA)),MATCH('Staff Info'!C4,Attendance!$A$10:$IG$10,0))&":"&INDEX(ADDRESS(COLUMN(DATA)+375,ROW(DATA)),MATCH('Staff Info'!C4,Attendance!$A$10:$IG$10,0))

Result: 'Attendance'!$AL$12:$AL$377, but it does not work as a range reference in Index or Match formula. e.g: =INDEX(Attendance!$A$12:$A$377,LARGE(ROW(Attendance!$B$12:$B$377)*(Attendance!$B$12:$B$377="Attend"),(COUNTIFS(Attendance!$B$12:$B$377,"Attend")-0))-ROW(Attendance!$A$12)+1) or, =INDEX(Attendance!A12:A377,MATCH(TRUE,INDEX(Attendance!AL12:AL377="Attend",0),))

I am also trying Attendance!$B$12:$B$377=cell no. "L5"

=INDEX(Attendance!$A$12:$A$377,LARGE(ROW(L5)*(L5="Attend"),(COUNTIFS(L5,"Attend")-0))-ROW(Attendance!$A$12)+1) "does not work"

Same as:

=INDEX(Attendance!A12:A377,MATCH(TRUE,INDEX(L5="Attend",0),)) "does not work"

Please, feedback me.

Thanks,

Bidduth