MrExcel.com - Your One Stop for Excel Tips & Solutions

Lookup Table Does Not Have to Be Sorted

April 06, 2022 - by

Lookup Table Does Not Have to Be Sorted

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.

Who can use the TRUE version of VLOOKUP? Scientists. Excel trainers, and Commission Accountants. It is likely 32 million times a year.
Figure 413. There are specific situations where you sort the lookup table.

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

But the exact match version of VLOOKUP (the one with False at the end) is done 187 Billion times a year.
Figure 414. 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:

A pie chart shows that 99.9826 % of the VLOOKUPs do not require the data to be sorted.
Figure 415. Most accountants will go their entire lives and never use the version of VLOOKUP that requires the table to be sorted.

This article is an excerpt from Power Excel With MrExcel

Title photo by Carl Beech on Unsplash