The Sad Truth About Excel’s VLOOKUP Function and a Happy End

The Microsoft Excel VLOOKUP() function is one of the most useful but confused spreadsheet functions. It allows you to look up a value in a range based on a value of an ‘index column’.

In the table below, you can see a standard product list:

If we wanted to retrieve the price of an item based on the Product ID, we would use the VLOOKUP() function, as seen here:

If you are already familiar with VLOOKUP then you know how powerful a function it is. And if you have just heard about it now – congratulations, you have become a better Excel user.

The problem with VLOOKUP()

VLOOKUP is very useful, but it also has a lot of limitations. One of the most obvious limitations is that you can only look up a value based on one parameter. Another obvious problem is that you can only find rows that exactly match the parameter. You can’t define logical rules such as ‘bigger than’, ‘smaller than’ and so on.

So, if we wanted to retrieve an employee from the following table. A deck hand that has 8 or more years of seniority, for example, we would not be able to use the VLOOKUP function.

Fortunately for us, there is a way to do perform this lookup:

This lookup formula is built from three parts:

  1. First we create an array, that has 1 column and as many rows as our lookup table. This array will hold the value 1 for each row in the lookup table that has the value ‘Deck Hand’ in the Role column and holds a number greater or equal in the ‘Seniority’ column.
    This is done by this expression:
    (C1:C9="Deck Hand")*1*(D1:D9>=8)*1

    When this expression is entered as a formula (you need to enter this formula with CTRL+SHIFT+ENTER because it is an array formula), it will return two arrays of 1s and 0s and multiply them. The result would be the array described above.
  2. In the second step, we use the MATCH() function to find the location (the index) of the cell that contains 1 within the array.
  3. We use the INDEX() function to retrieve the employee name from column B, based on the index of the row that we discovered above.

Summary

If you found it difficult to follow the above, the best approach to learning this technique is to give it a try in your own project. You will quickly see how powerful it is!

This formula template can be used to retrieve values based on any number of parameters and is a de-facto, multi-parameter VLOOKUP function.

Hope it helps.

Karen

P.S.

Don’t forget to check out our PDF To Excel Converter. It can save you a lot of precious time you now spend on retyping PDF data.