Forum Discussion
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 fi...
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.
Ooops - just found it up higher on your first response.