Excel - Why Does VLOOKUP(True) Need The Table To Be Sorted - Episode 1925
3,261 views · Published 12 September 2014 · 7:13 · Indexed 6 November 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Exactly how a binary search works in Excel VLOOKUP. Welcome to the MrExcel podcast. In this episode, we will be discussing the VLOOKUP(True) Binary Search Method and why it may be giving you the wrong answer. This is a topic that was brought up by one of our listeners, Rahim, who was experiencing issues with his VLOOKUP(True) formula. So, let's dive in and explore why this is happening and how to avoid it. First of all, let's clarify that the VLOOKUP(True) method is not commonly used. In fact, in a Twitter poll, 99.999% of respondents said they use the VLOOKUP(False) method. The only time the VLOOKUP(True) method is used is when creating reference tables or in specific edge cases. So, if you are experiencing issues with your VLOOKUP formula, chances are you are using the VLOOKUP(False) method. Now, let's get into the nitty-gritty of why the VLOOKUP(True) method may be giving you the wrong answer. The VLOOKUP(True) method uses a Binary Search, which means it splits the table at the midpoint and then searches for the value. This works fine when the table is sorted, but if it is not sorted, the results can be unpredictable. This is because the midpoint cell controls which half of the table the search will look into, and if the table is not sorted, the midpoint cell may not be accurate. To put it simply, if you are using the VLOOKUP(True) method, your table must be sorted for it to give you the correct answer. This is not the case with the VLOOKUP(False) method, where sorting does not matter. So, if you are experiencing issues with your VLOOKUP formula, make sure you are using the correct method and that your table is sorted. I hope this explanation has cleared up any confusion about the VLOOKUP(True) method and why it may be giving you the wrong answer. Remember, always follow the rules and sort your table when using the VLOOKUP(True) method. Thank you for tuning in to the MrExcel podcast, and don't forget to check out our Excel class, "Excel formulas - The Dirty Dozen" at tinyurl.com/edupow5. See you next time on MrExcel. 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) Explanation of VLOOKUP(True) (00:33) Explanation of VLOOKUP(True) with unsorted data (01:19) Discussion on the use of VLOOKUP(True) (02:27) Explanation of why VLOOKUP(True) gives wrong results with unsorted data (03:38) Demonstration of Binary Search method for VLOOKUP(True) (05:37) Explanation of how Binary Search works with even number of rows (06:28) 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 #excelvlookup #lookup #xlookup #excelsort This video answers these common search terms: exactly how a binary search works in excel lookup why does vlookup(TRUE) need the table to be sorted how to use vlookup in excel binary search how to do approximate match in excel what is an approximate match in excel vlookup how to do an approximate match vlookup in excel how to v lookup a range of values in excel +Rahim Zulfiqar Ali asks why VLOOKUP(,,,True) returns certain wrong answers when the table is not sorted. The better question would be how it returns the right answer at all when you break the rules. Today, I explain the gory details behind the Binary Search used by the VLOOKUP(,,,True) and show how manipulating key values at the quartiles of the lookup table can send the VLOOKUP into the wrong quadrant of the table. Bottom line: Don't use the VLOOKUP(,,,True) with an unsorted table. Or.... just use VLOOKUP(,,,False) like 99% of the rest of the world. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153751/
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