Excel - How To Find Three Values In Sequence In Excel - Duel 165 - Episode 1926
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-...
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...
Смотрите видео Excel - How To Find Three Values In Sequence In Excel - Duel 165 - Episode 1926 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 26 Сентябрь 2014, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 3,390 раз и оно понравилось 34 людям.