Look Who it Was Random video

Excel - Three-Way Lookup - Excel - Episode 1328

2,017 views · Published 21 January 2011 · 9:32 · Indexed 14 October 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: 3-Way Lookup in Excel. Arranging 2 Criteria with Multiple Ranges to give us our result, Mike and Bill use the INDEX, INDIRECT and MATCH Functions to lookup the data.

Welcome to another exciting episode of Dueling Excel! In this episode, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun tackle a challenging three-way lookup problem. The task at hand is to find a value in one of four identical worksheets, based on a given cell value, and then perform a two-way lookup to find the corresponding value in that worksheet. At first, it seemed like a daunting task, but Bill noticed that all four worksheets had the same values in columns A and B, making it a perfect opportunity to simplify the solution.

Bill's initial thought was to combine all four worksheets into one, but since that was not an option, he came up with a clever solution using a combination of formulas. He created a formula to determine which section the given cell value falls into, and then used the INDIRECT function to dynamically reference the correct worksheet and cell to retrieve the desired value. This solution may not be suitable if the table is subject to frequent changes, but for this particular scenario, it was a quick and effective solution.

Mike was equally impressed with Bill's solution and decided to take a slightly different approach. He looked at the problem as having two criteria to look up and multiple ranges to search within. To simplify the process, he used the MATCH function to create a single column from the two criteria and then used the INDEX function to perform the lookup. He also utilized the INDIRECT function to dynamically reference the correct worksheet and column, making it a versatile solution that would work with any table, even if changes were made.

In the end, both solutions proved to be effective in solving the three-way lookup problem. Bill's solution was specific to this particular scenario, while Mike's solution was more versatile and could be applied to any similar problem. As always, there are multiple ways to solve a problem in Excel, and it's always exciting to see the different approaches that Bill and Mike take in their duels. Stay tuned for more exciting episodes of Dueling Excel from MrExcel and Excel Is Fun!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Two-way lookup required
(00:29) Realization of identical worksheets
(00:39) Solution using formulas
(01:01) Alternative solution using named columns
(02:07) Comparison of solutions
(03:15) Explanation of alternative solution
(04:04) Creation of named columns using keyboard shortcuts
(05:05) Use of INDIRECT function to convert text strings to references
(06:05) Explanation of two criteria lookup
(07:15) Creation of single column from two using array formula
(08:21) Alternative solution without array formula
(09:10) Clicking Like really helps the algorithm

This video answers these common search terms:
BridgeSpan
Combining worksheets
Dueling Excel podcast
Engineering numbers
Formulas
Grabbing values from different sheets
Indirect function
Look up
RailwaysSpan
Simple solution
Worksheets
YouTube video

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/dueling-excel-three-way-lookup-podcast-1328.1153008/

 About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.

More from this channel