Look Who it Was Random video

Excel - Dueling Excel - Find First Over 100 Duel 150 - Episode 1855

4,804 views · Published 7 February 2014 · 6:43 · Indexed 27 September 2026

Channel: MrExcel.com · 2014 · Education

Watch on YouTube

Microsoft Excel Tutorial: Dueling Excel - Find First Over 100 Duel 150.

Welcome to another exciting episode of Dueling Excel! In this episode, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun will be tackling a challenging problem - finding the first value over 100 in a column and returning the corresponding value from another column. This may seem like a simple task, but as Bill and Mike will show you, it can be quite tricky.

Bill and Mike will be using their expertise in Excel to come up with different solutions to this problem. Bill will share his initial attempts, where he came up with 39 solutions but none of them worked. He will then demonstrate his final solution, which involves using the IF and MIN functions to find the first value over 100 and return the corresponding value from another column. While this solution works, Bill will also mention that it would be nice if there was a way to do this using the VLOOKUP function.

Mike will then share his solution, which involves using the INDEX and MATCH functions. He will explain how he uses the INDEX function to create an array of values and then uses the MATCH function to find the first TRUE value in that array. This solution is elegant and beautiful, as Bill puts it, and it also avoids the use of Control + Shift + Enter. Mike will also show a clever trick where he uses the INDEX function with a blank row to avoid having to use Control + Shift + Enter.

So, whether you're a beginner or an advanced Excel user, this episode of Dueling Excel will have something for everyone. Bill and Mike will show you different approaches to solving a seemingly simple problem, and you'll learn some useful tips and tricks along the way. So sit back, relax, and enjoy this exciting episode of Dueling Excel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next week for another exciting episode!

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) Find first over 100 in Excel
(00:14) Multiple failed attempts
(00:24) Solution using IF and MIN functions
(01:35) Solution using INDEX and MATCH functions
(02:46) Elegant and beautiful formula
(06:24) 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:
Excel array calculation
Excel array formula
Excel array operation
Excel column A
Excel column B
Excel Ctrl+Shift+Enter
Excel IF function
Excel INDEX function
Excel MATCH function
Excel MIN function
Excel row number
Excel VLOOKUP greater than 100


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


Look through the numbers in column B. When you find the first number greater than a hurdle value of 100, return the number from the corresponding value in column A. This Dueling Excel episodes offers tricks with INDEX, MIN, and MATCH.

More from this channel