Forum Discussion
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...
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)
Oh, of course that's how to make it non text.
Now it works like a charm 🙂 and i'm sure there is a simpler way to solve it as well :D.
=INDIRECT(ADDRESS(MATCH(VLOOKUP($A2;$M$2:$N$29;2;FALSE);'Sheet1'!$A:$A;0)+2;MATCH(LEFT($B2;7);INDIRECT("'Sheet1'!"&ADDRESS(MATCH(VLOOKUP($A2;$M$2:$N$29;2;FALSE);'Sheet1'!A:A;0)+1;3;1;1)&":"&ADDRESS(MATCH(VLOOKUP($A2;$M$2:$N$29;2;FALSE);'Sheet1'!A:A;0)+1;50;1;1;));0);1;1;"Sheet1");TRUE)
Great thanks!
Now it works like a charm 🙂 and i'm sure there is a simpler way to solve it as well :D.
=INDIRECT(ADDRESS(MATCH(VLOOKUP($A2;$M$2:$N$29;2;FALSE);'Sheet1'!$A:$A;0)+2;MATCH(LEFT($B2;7);INDIRECT("'Sheet1'!"&ADDRESS(MATCH(VLOOKUP($A2;$M$2:$N$29;2;FALSE);'Sheet1'!A:A;0)+1;3;1;1)&":"&ADDRESS(MATCH(VLOOKUP($A2;$M$2:$N$29;2;FALSE);'Sheet1'!A:A;0)+1;50;1;1;));0);1;1;"Sheet1");TRUE)
Great thanks!