What is VLOOKUP?

VLOOKUP stands for ‘Vertical Lookup’. It is a function that makes Excel search for a certain value in a column (the so called ‘table array’), in order to return a value from a different column in the same row. 

About the VLOOKUP Function

A VLOOKUP function exists of 4 components: 

  1. The value you want to look up;
  2. The range in which you want to find the value and the return value;
  3. The number of the column within your defined range, that contains the return value;
  4. 0 or FALSE for an exact match with the value your are looking for; 1 or TRUE for an approximate match.

Syntax: VLOOKUP([value], [range], [column number], [false or true])

VLOOKUP Example

In our example we have a list of fruits, the amount in stock and the current price. We want to find the price of kiwi’s quickly in this table.

First, select a cell in which you want to publish the current price:

What is VLOOKUP - Select a Cell :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

So, in our selected cell we start typing: =VLOOKUP( :

What is VLOOKUP - Function Start :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Then we select the value we want to look up, in this case it’s ‘Kiwi’ in cell B12:

What is VLOOKUP - Lookup Value :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Next we enter ‘;’ and select the range (or table array) in which we want to find the lookup value and the return value. In this case it is range (A2:C10):

What is VLOOKUP - Table Array :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Then we enter ‘;’ and the number of the column within the defined range in which we want to look for the return value. In this case it is column 3, followed by ‘;’ and ‘0’ or ‘FALSE for an exact match with the lookup value ‘Kiwi’:

What is VLOOKUP - FALSE :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

When we press enter, we get the corresponding price from the row that contains value ‘Kiwi’ within the selected table array:

What is VLOOKUP - Exact Match :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

In this example we only have a small list of fruits, but imagine you have a long list of data and you wish to use a certain value elsewhere in your spreadsheet. Using VLOOKUP (and HLOOKUP) the user only has to change a certain value in one worksheet and it will automatically be changed in all other relevant places.

But what if we’re not certain about the lookup value? This is when the approximate match might be useful.

Approximate Match in VLOOKUP

When we enter the word ‘Kwii’ in our lookup value cell B12, the VLOOKUP function as we designed it gets confused, because it can’t find this value in the table array. It returns the standard Excel error #N/A:

What is VLOOKUP - Standard Excel Error :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

But when we change the ‘0’ (or ‘False’) component of our VLOOKUP function to ‘1’ or ‘TRUE’:

=VLOOKUP(B12; A2:C10; 3; 1)

Now we have asked the function to look for an approximate match with ‘Kwii’. If it can’t find the exact match, it will look for the closest match to the value ‘Kwii’, less than ‘Kwii’, which is ‘Kiwi’:

What is VLOOKUP - Approximate Match :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Would we enter ‘Kawi’, it will return €0,23, because then ‘Banana’ is the closest match less than ‘Kawi’ (based on the alphabet).

Try PerfectXL Now

 

About PerfectXL

PerfectXL provides full automatic clarity about all risks, mistakes and other problems in your spreadsheets, plus an extensive improvement plan. You don’t have to be an expert yourself and you don’t need much time to fix your file.

Learn about the features

 

Prefer INDEX and MATCH over VLOOKUP

VLOOKUP is used a lot more often than the INDEX and MATCH combination, while the latter are really much better. INDEX and MATCH are less error prone and a lot more efficient.
INDEX & MATCH!

Subscribe to our Excel Guidelines