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 pull numbers for each of my technicians into a spreadsheet from multiple excel files that are sent each month. I muddled through a few web pages on XLOOKUP and was able to get the above to work. The screenshot is just a snip-it of the file I'm working in, and it goes out to column U with all the various categories (nothing crazy). Since I have to do this for monthly reports and the quarterly report, I have the same little snip it copied 4 times on the same worksheet (tab?).
It's one thing to have to set this up once copying the above formula and just having to change what destination column is referenced "!$D:$D", but having to change the destination file name twice for each column that is using this formula is killing me as the file names are always changing.
Is there a way I can modify this to use a cell reference to somewhat automate this? I would like to be able to type in the file name into say cell D2 and it use that to complete the formula.
Example:
=XLOOKUP([@Name],'[D2]Individual Tech Summary'!$A:$A,'[D2]Individual Tech Summary'!$D:$D)
and on cell D2 it would have either "[May 2025 Metrics.xlsx]" or "May 2025 Metrics.xlsx". I hope this makes sense? That way I can just update the name for June 2025, July 2025, Q3 2025, Q4 2025, March 2029... you get the idea I hope.
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"))
4 Replies
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.
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"))
