Excel: Lookup Table Does Not Have to Be Sorted

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I think the lookup table has to be sorted.

Strategy: I don't care what your professor said, if you are using the ,FALSE version of VLOOKUP, the lookup table does not have to be sorted.

Since 99.9826% of VLOOKUPs have FALSE at the end, the table does not have to be sorted in 5759 out of every 5760 cases.

Sure, when you don't have FALSE or 0 at the end of your lookup table, then the table has to be sorted.

  1. There are specific situations where you sort the lookup table.

    However, most of the time, the lookup table does not have to be sorted.

  2. Most people don't have to sort the lookup table.

    In case you are more of a visual person instead of a number person, here is the pie chart:

  3. Most accountants will go their entire lives and never use the version of VLOOKUP that requires the table to be sorted.

For more resources for Microsoft Excel