Forum Discussion

PaulMcK007's avatar
PaulMcK007
Copper Contributor
Jul 06, 2025
Solved

Substituting Text in Excel Lookup Formula

I'm keeping game weekly statistics in Excel.  I have the Lookup formula below:

=LOOKUP(7,[WeekX.xlsx]Game!$A:$A,[WeekX.xlsx]Game!$B:$B)

The "7" refers to a player number.  I'm using Week1 in the first week, Week2 in the second week, etc.

I have many of these Lookup formulas in the one spreadsheet.

I would like to substitute "WeekX" where the "X" can point to a spreadsheet cell value (i.e. 1 or 2 or 3, etc.)

I've tried double quotes "" and Concat in the Lookup formula.  I have not been successful.  Any suggestions?

Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jul 07, 2025

    INDIRECT requires it to be text.  And that solution is assuming the File names you are looking up are in the format WeekX.xlsx and the X is replaced by the value in cell C1. I'm also pretty sure this will require those workbooks to be open.  If so try this:

    =LOOKUP(7,INDIRECT("[Week"&$C$1&".xlsx]game! $A:$A"),INDIRECT("[Week"&$C$1&".xlsx]Game! $B:$B"))

    is is possible to do it all in the same workbook?  you could also use power query to pull all the data from the other workbooks into a table in this workbook and then you can just do a look up in that table.

8 Replies

  • MsBoJenkins's avatar
    MsBoJenkins
    Iron Contributor
    Jul 07, 2025

    Change the formula to =LOOKUP(7,INDIRECT([Week&$C$1&.xlsx]game! $A:$A),INDIRECT([Week&$C$1&.xlsx]Game! $B:$B)).

    • PaulMcK007's avatar
      PaulMcK007
      Copper Contributor
      Jul 07, 2025

      Thanks for this suggestion.  I entered the formula as suggested above, and I got an error message

       

      and when pressing OK, I got this:

       

       

      Apparently INDIRECT does not like the 'ref_text' to start with an "[".  Any suggestions?

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Jul 07, 2025

        INDIRECT requires it to be text.  And that solution is assuming the File names you are looking up are in the format WeekX.xlsx and the X is replaced by the value in cell C1. I'm also pretty sure this will require those workbooks to be open.  If so try this:

        =LOOKUP(7,INDIRECT("[Week"&$C$1&".xlsx]game! $A:$A"),INDIRECT("[Week"&$C$1&".xlsx]Game! $B:$B"))

        is is possible to do it all in the same workbook?  you could also use power query to pull all the data from the other workbooks into a table in this workbook and then you can just do a look up in that table.

Resources