Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel

Look up values from multiple sheets with one formula: VLOOKUP or INDEX MATCH plus INDIRECT. 📥 Download the practice workbook: https://www.xelplus.com/lookup-values... 🎓 500,000+ enrolled in our courses. Learn XLOOKUP and the other modern lookup functions in Master Excel's Essential Modern Functions 👉 https://www.xelplus.com/course/new-ex... A summary sheet pulls each division's invoiced amount from that division's own sheet, based on the selected month. A nested IF would need editing every time a sheet is added. INDIRECT avoids that. It turns the sheet name in a cell into a real reference, so one formula copied down finds the right sheet on its own. There are two versions. The first uses VLOOKUP with sheet names and cell references. The second uses INDEX MATCH with Excel table names. Table names can't contain spaces, so SUBSTITUTE swaps the spaces for underscores to match. Good to know: Sheet names with spaces need single quotes around them inside INDIRECT. INDIRECT recalculates with every change in the workbook, which can slow down large files. If a sheet or table is renamed, the name in the summary cell has to change too. Version note: In current Excel, the table name box is on the Table Design tab. XLOOKUP can replace VLOOKUP or INDEX MATCH in the same INDIRECT setup in Microsoft 365, Excel 2021 and Excel 2024. 00:00 Look up values across multiple sheets 00:29 Example: pull values from each division's sheet 01:13 Why not use a nested IF 01:43 Two methods: sheet names or tables 02:19 Start with VLOOKUP on one sheet 04:13 How INDIRECT works 05:24 Single quotes for sheet names with spaces 05:48 Make the sheet name dynamic with INDIRECT 07:47 Convert the data to Excel tables 08:07 Name each table (underscores instead of spaces) 08:39 INDEX MATCH with a table reference 09:57 Make the table name dynamic with INDIRECT 11:06 Replace spaces with SUBSTITUTE 12:31 Add new data and test both methods 🎬 VLOOKUP explained:    • Don’t Use VLOOKUP Until You Watch This! (V...   🎬 INDEX MATCH basics:    • How to use Excel Index Match (the right way)   🎬 INDIRECT explained:    • Excel INDIRECT Function: Lookup Values in ...   ➡️ Join this channel to get access to perks:    / @leilagharani   ✉️ Get one practical Excel tip every week in our free newsletter, Between the Sheets: https://link.xelplus.com/yt-d-newsletter 🚩 Connect with us: Instagram:   / lgharani   LinkedIn:   / leilagharani   #excel #exceltips #excelformulas