Forum Discussion
Solved
Help with XLOOKUP
=XLOOKUP([@Name],'[May 2025 Metrics.xlsx]Individual Tech Summary'!$A:$A,'[May 2025 Metrics.xlsx]Individual Tech Summary'!$D:$D) Let me say thanks in advance for any help with this. I have to pu...
If the other workbook will be open in Excel too, you can enter its file name in D2 (in the form May 2025 Metrics.xlsx), and change the formula to
=XLOOKUP([@Name], INDIRECT("'["&D2&"]Individual Tech Summary'!$A:$A"), INDIRECT("'["&D2&"]Individual Tech Summary'!$D:$D"))
Thank you SO SO much, to say this is LIFE CHANGING for me is an understatement!
I did have an issue with it changing the "D2" cell name to D3, D4, etc.. when trying to drag and copy it. I found the solution is to add $ before the "D" and before the "2" and it will lock that cell value in (don't ask how long it took me to figure this out lol).
Also for anyone looking at this I have the "May 2025 Metrics.xlsx" file in the same folder on my desktop as the excel file I'm putting this formula in so you will need to keep all them in the same place (I think?).
Also just wanted to point out that "Individual Tech Summary" is the name of the (tab/sheet) within my May 2025 Metrics file that has techs data that I need to pull in, and it's looking at all of column "A" on that sheet to match to "@name". Wow I could not be a technical writer... and then the second part with the $D:$D is the column on that same sheet with the actual data I want pulled in for that tech.
The below is the screenshot I should have put in the original post, and everything is the same except it will reference to D46 instead of D2, and has the $ added as stated above.
The final solution is:
=XLOOKUP([@Name], INDIRECT("'["&$D$46&"]Individual Tech Summary'!$A:$A"), INDIRECT("'["&$D$46&"]Individual Tech Summary'!$D:$D"))
Good to hear it worked for you.
It doesn't really matter whether the workbook referred to is in the same folder as the workbook with the formula or not. INDIRECT only works if the other workbook is open in Excel; if that workbook is closed, INDIRECT will return #REF!
I tested this an you are correct all I am getting is the #REF, is there any way to make this work if the other file is not open? I will be ref about 6 -8 files to populate this spreadsheet. Again thank you for your assistance with this.