Excel - How To Find Three Values In Sequence In Excel - Duel 165 - Episode 1926
3,689 views · Published 26 September 2014 · 7:08 · Indexed 22 October 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Search through a range to find three values in exact sequence. Welcome to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to find the best solution to your Excel problems. In this episode, we tackle a tricky challenge sent in by a viewer on YouTube: how to find three consecutive items in a list using the MATCH function. As always, we have some fun banter and a special treat for our viewers - a brand new theme song and animation created by the talented Szilvia Juhasz. But before we dive into the challenge, let's take a moment to appreciate the amazing work done by Szilvia, Mike, and Val. The catchy tune and illustrations in the new theme song are sure to get stuck in your head. And let's not forget Szilvia's impressive Excel skills, which we have seen in previous episodes such as "Santa Baby." Now, let's get down to business and see how we can solve this tricky problem. The challenge is to find three consecutive items in a list using the MATCH function. We start by using the CONCATENATE function to combine the three cells containing the items we want to find. Then, we use the MATCH function to search for this concatenated value in a list of items. But here's the twist - the list is split into three separate sections, and we need to search for the value in all three sections. To do this, we use the ampersand (&) to join the three sections and add a "0" at the end to ensure an exact match. But wait, there's more! Mike has a surprise for us - a solution that works even if there are duplicates in the list. He uses a clever trick with the Ctrl+Shift+Down arrow shortcut to select the entire list in one go. This is just one example of the amazing Excel skills that Mike possesses. I, on the other hand, am left in awe and have to admit defeat. But hey, that's what Dueling Excel is all about - learning from each other and finding the best solutions to Excel problems. So, there you have it - another exciting episode of Dueling Excel. We hope you enjoyed watching us tackle this challenge and learned some new Excel tricks along the way. Don't forget to check out the new theme song and animation by Szilvia, and let us know in the comments what you thought of it. And as always, keep sending in your Excel problems and challenges, and we'll do our best to find the best solutions for you. Thanks for watching, and we'll see you in the next episode of 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) Theme Music (01:00) Lookup to find 3 cells in a row (01:23) Question from YouTube: searching for values in a matching list using 3 consecutive cells (02:13) Solution using =MATCH and concatenation (03:02) Mike's reaction and inability to find a solution (04:12) Mike's creation of Bill's solution (05:05) Discussion on keyboard shortcuts and a potential contest (06:23) 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 #exceltextjoin #excelvlookup #lookup #xlookup #excelconcatenate This video answers these common search terms: how to use concatenate with vlookup in excel how to find three values in sequence in excel find three cells that contain a certain three values build a vlookup range that is concatenatedhow to use concatenate with vlookup in excel, how to find three values in sequence in excel, find three cells that contain a certain three values, build a vlookup range that is concatenated Three names in 3 adjacent cells - search through a lookup table to find those exact three cells in sequence. This episode shows an array formula to solve the problem. Also - check out our new theme from Excel guru Szilvia Juhasz. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153752/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920