Forum Discussion

asd6231's avatar
asd6231
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
  • asd6231's avatar
    asd6231
    Copper Contributor
    Apr 02, 2019

    Twifoo 

     

    Hey, thank you, this worked in the end!  I swear I'd tried using CELL "address" before, but it did finally work.

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Apr 02, 2019
      I’m delighted to learn that.
    • kellygneiting's avatar
      kellygneiting
      Copper Contributor
      Oct 11, 2019

      asd6231 Can you please give me the actual formula?  Thank you!

  • AntsXL's avatar
    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!

    • mtarler's avatar
      mtarler
      Silver Contributor
      Oct 29, 2020

      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)
      • AntsXL's avatar
        AntsXL
        Copper Contributor
        Oct 29, 2020
        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!
    • BidduthD's avatar
      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

       

Resources