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.
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:
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.
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.