Revisit VLOOKUP, You may Be Missing some Important Point

Explore Vlookup in a new way so you make sense of old arguments in a innovative and enlightening ways.

The fact is that humans VLOOKUP everyday! Excel users too!

The fact is that you search information day by day and every occasion to decide and take actions. I found very useful to make sense about this great Excel LOOKUP function VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) in a different way?

You will see that using this Excel LOOKUP function is a big deal but has a lot of limitations.

Let’s see…

Imagine you are planning to go to the local theater with your wife. You want to see “Titanic”.

Well, you are in the web site of the theater to get the information about the movie…

Here’s the sequence of the use of Excel VLOOKUP Function in real life:

  • You search the Genre, Summary, Duration, etc. That is, the attributes (col_index_num) for “Titanic” (lookup_value)
  • The system searches “Titanic” in its list of movies (table_array) in the appropriate index column (the leftmost for VLOOKUP)
  • The system returns the attributes for “Titanic” only (range_lookup = exact match)
    • Genre= Drama
    • Summary= the history of the transatlantic that sank…
    • Duration= 3 hours

In brief, the components of Excel VLOOKUP Function were:

  • lookup_value =”Titanic”
  • table_array = movies
  • col_index_num = Genre, Summary, Duration (this is the information that matters to you)
  • range_lookup= 0 (exact match)

You now can take a decision based on the provided information for “Titanic”.

That’s not all…

The problems arise when your wife wants to search…

  • The movie that start near 21:00
  • The movie that ends before 23:00
  • The movie that is “Horror” and is debuting
  • The movie that is “Horror” and ends before 23:000
  • And many more…

That’s why VLOOKUP has limitations; the fact is that your reporting purposes go beyond a simple exact match based on a left index column. You should be able…

  • To search data based on multiple conditions
  • To search data based on advanced conditions
  • To search data based on any index column available at the table array (without moving it to the left each time you require to do it)

You got the point, VLOOKUP is not enough.

Say welcome to INDEX and MATCH, VLOOKUP + Helper Column, SUMPRODUCT, Array Formulas. They will allow you to do advanced reporting; just extract the data you want from tables using these Formulas.

If you are equipped with Vlookup as the unique solution, you will struggle when you face Excel Lists which contain multiple and complicated criteria…

Conclusion

VLOOKUP is really one of the most important formulas in Excel. It is usually the first formula that put you in contact with the power of Excel; however, VLOOKUP is often used for things it was not designed for.