2D LOOKUP and the BYROW function

In this video, we dive into a powerful Excel technique using the BYROW function to perform 2D lookups across multiple columns — ideal when data isn’t neatly packed into a single column. Starting with a simple XLOOKUP, we quickly escalate the challenge: how do you find someone's department when their email could be in any column? The video walks through using BYROW with FILTER and LET functions to create a dynamic, flexible lookup that can handle inconsistencies like different row counts and missing matches, with clear error handling and even a warning system. Plus, it shows how to wrap everything into a reusable LAMBDA function to save time on future projects. Along the way, shoutouts to an Excel MVP and Reddit user who inspired this solution showcase the power of community learning! You'll also see a practical introduction to using Excel Labs for managing Lambdas more effectively, and tips for saving your custom functions to GitHub for easy reuse. Whether you’re building smarter lookups or looking to supercharge your Excel workflows with Lambdas, this tutorial is packed with tips you’ll want to bookmark. LAMBDA GIST: https://gist.github.com/wynhopkins/2efdf218ed78be03a0a082ff6a99dad1 GIST explanation and DIVIDE: https://youtu.be/8kWYiUjTvAE FILE TO DOWNLOAD: https://aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/EqGdwRMC3y5Lt_dE2AdHJwsBGJTc-Ax17NlawNaymAh35A?e=Yb7u4y 🧑🏻‍🎓Learning Power BI? Check out our online course https://accessanalytic.thinkific.com/ 📗 Did you know I've written a book "Power BI for the Excel Analyst"? https://pbi.guide/book/ The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. 🌐 https://accessanalytic.com.au/ 🫶🏻 Join the channel to get access to perks: https://www.youtube.com/channel/UCvD2rJJrp0aS48-KCk0eMHQ/join 00:00 Introduction 01:09 Solving multi-column lookups 02:00 Shoutouts for inspiration 02:47 Why BYROW beats OR for row-wise checks 06:30 Building the formula with LET 08:58 Error Handing with Row Count Check 10:51 Handling no match 11:50 Turning the formula into a reusable LAMBDA 14:16 Use Excel Labs! 16:20 Importing from GIST 17:38 Using BYROW for spilling array-based formulas ( if they return single values )