Uploaded May 2025 | Updated September 2026, 2 weeks ago
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:
gist.github.com/wynhopkins/2efdf218ed78be03a0a082ff6a99dad1
GIST explanation and DIVIDE: youtu.be/8kWYiUjTvAE
FILE TO DOWNLOAD:
aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/EqGdwRMC3y5Lt_dE2AdHJwsBGJTc-Ax17NlawNaymAh35A?e=Yb7u4y
🧑🏻🎓Learning Power BI?
Check out our online course
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:
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 )
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:
gist.github.com/wynhopkins/2efdf218ed78be03a0a082ff6a99dad1
GIST explanation and DIVIDE: youtu.be/8kWYiUjTvAE
FILE TO DOWNLOAD:
aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/EqGdwRMC3y5Lt_dE2AdHJwsBGJTc-Ax17NlawNaymAh35A?e=Yb7u4y
🧑🏻🎓Learning Power BI?
Check out our online course
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:
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 )
