Forum Discussion

indybell's avatar
indybell
Copper Contributor
Oct 08, 2022

XLOOKUP across many sheets

Hi All, 

 

I have an xlookup function as shown below.

 

=XLOOKUP(C35, SydBankstown_AllYears!$F$2:SydBankstown_AllYears!$F$2000, SydBankstown_AllYears!$L$2:SydBankstown_AllYears!$L$2000)

 

Each weather station (e.g. SydBankstown_AllYears) is on a separate sheet. Each row references a different weather station and we have a cell saying which sheet to reference. 

 

This function works but I need to replace all of the hardcoded sheet references with a cell reference to the appropriate sheet as there are 6000 data points and I can't write in the appropriate sheet for each datapoint. Does anyone know how to do this? All I can see is using indirect() but I cant get it to work for this formula? 

excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Oct 08, 2022

    indybell 

    Hello you can do the following to create your functions without INDIRECT().

    Go to an auxiliary column (which you can delete later) and assemble the formula there as text.

    In my example I have assumed that the sheet name is in column D.

     

    =CONCAT("=XLOOKUP(C",ROW(),",",D35,"!$F$2:",D35,"!$F$2000,",D35,"!$L$2:",D35,"!$L$2000)")

    Then you have your formula as text in the auxiliary column. Of course, this does not work yet.

    Now you can copy the cells with the formula texts and paste them into another column as text. Now you have the formula correctly in the cell - but still as text. To make the formula work, you only have to use the command "Text to columns" from the Data menu once without separators and voila, the text has become a formula that works.


    Good luck

     

Resources