A client recently came to me with a challenge: Their workbook contained three separate worksheets, each with the same column headings (EmployeeID, Name, Salary, etc.), but a different numbers of rows. They needed a way to search for an employee’s details, no matter which sheet they were on.
The key to solving this problem? VSTACK.
By using the VSTACK function inside XLOOKUP, I was able to merge the data from all three sheets into a single virtual list. This allowed XLOOKUP to search across multiple sheets seamlessly.
This method is not only efficient but also dynamic, automatically updating when new data is added.
Want to see exactly how it works? Check out the video where I walk through the entire process step by step…
Formulas used in the video:
- =VSTACK(UK!A2:G27,France!A2:G14,Spain!A2:G29)
- =XLOOKUP(B1,VSTACK(UK!A2:A27,France!A2:A14,Spain!A2:A29),VSTACK(UK!A2:G27,France!A2:G14,Spain!A2:G29))
Download a copy of the file(s) used in this video: https://share.zight.com/wbudjY4x