Look Who it Was Random video

Excel - Which Rows Contain the Match - Dueling Excel - Episode 1139

304 views · Published 6 November 2009 · 6:10 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Which Rows Contain the Match - Dueling Excel.

Welcome to another exciting episode of Dueling Excel, where Mike Girvin from ExcelIsFun and I, Bill Jelen from MrExcel, go head to head to solve an Excel challenge.
In this episode, we tackle a question sent in from Portugal. The challenge is to create a formula that will return the value from the left-hand side of a matrix, based on a dropdown selection and the location of an "X" in the corresponding column.
I start off by using the MATCH function to determine which column the dropdown selection is in. This allows us to easily point to the correct column using the OFFSET function.
However, Mike's three-year-old son Isaac suggests using the INDEX function instead of OFFSET. So, we give it a try and it works just as well!
The trick to using INDEX to extract a column is to use a row number of 0. This tells INDEX to return all the rows in that particular column.
Next, we use the MATCH function again, this time within the INDEX function, to find the row number of the "X" in the selected column.
Finally, we use one more INDEX function to return the value from the left-hand side of the matrix, using the row number determined by the previous MATCH function.
And just like that, we have a formula that can handle this 3-way lookup challenge!
But don't worry, we also show you how to combine all these functions into one long formula, for those who prefer a more compact solution.
So, join us as we go back and forth, trying out different approaches and ultimately coming up with a solution that works. And don't forget to check out Isaac's hilarious Excel tips on YouTube - he may just give us a run for our money in the future! Thanks for watching and we'll see you next time on Dueling Excel.

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) Introduction by Bill and Mike
(00:24) Question from Portugal
(00:46) Building the solution in steps
(01:02) Using MATCH to find the column
(01:12) Using OFFSET to point to the correct column
(01:33) Using INDEX to return the value on the left-hand side
(02:23) Mike's alternative solution using INDEX instead of OFFSET
(03:07) Mike's explanation of his 3-way lookup method
(05:48) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Alter Data tab
City obfuscation
Cost column obfuscation
Delta IV Heavy rocket launch
Excel add-in
Increase and decrease buttons
Learn Excel
Obfuscate Data in Excel
Randomize first names
Randomize states
Sales rep names

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152735/



A viewer from Portugal sends in a question about finding which row contains the match. Episode 1139 shows you a few ways to go!

More from this channel