Apr 01 2019 04:54 AM
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!
Apr 01 2019 07:50 AM
Apr 02 2019 03:54 AM
Hey, thank you, this worked in the end! I swear I'd tried using CELL "address" before, but it did finally work.
Apr 02 2019 06:06 AM
Oct 11 2019 10:01 AM
@asd6231 Can you please give me the actual formula? Thank you!
Oct 29 2020 01:29 PM - edited Oct 29 2020 01:34 PM
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!
Oct 29 2020 01:49 PM
@AntsXL that is because you are putting TEXT in there instead of a cell reference. You need to add the INDIRECT() function to convert that TEXT into a cell reference. should be something like:
=MATCH(LEFT(B2;7);INDIRECT("'Sheet1'!"&ADDRESS(E7+1;3;1;1;)&":"&ADDRESS(E7+1;50;1;1;));0)
Oct 29 2020 03:37 PM
Sep 12 2024 01:59 PM
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