Data Analyst and Technical Consultant

VLOOKUP and IFERROR

VLOOKUP lets Excel do the hard work of matching up a value from one spreadsheet to another.  It saves time and is an essential Excel tool.

VLOOKUP

  • Insert a column to the right of the column you are using as the reference.  This is where your formula and results will go.
  • Type into your formula bar =VLOOKUP( and complete the formula using the instructions below

 

The formula means:  

=VLOOKUP(
lookup_value - reference cell,
table_array - the range of columns to look in,
index_num - column number of the range where the result is,
range_lookup - exact match or closest match)

 

  • lookup_value -You define a value for the formula to look for – for example, the custName in B2 below.

 

  • table_array - It looks for a match of the “lookup_value” in the first column of the “table_array”.  This is where you go to the table you want results returned from and highlight the whole column when the reference is and drag over to where the value you want to be returned is.

 

  • index_num - It will return the value in the column you specify using the “index_num”.  This is where you need to enter the number of columns the result is from the reference.  In the example, we use the number ‘2’ for the second column away from the reference column.

 

  • range_lookup - The “range_lookup” is a TRUE or FALSE value. If you put 1 it will give you the closest match. If you put 0 it will only give you an exact match.  It’s recommended to use an exact match..

 

  • All done!  Remember to copy and ‘paste values’ to ensure you the results remain and not the formula.  If for any reason your lookup table moves or is deleted, so will your results.

 

 

Bonus tip:

To hide the #N/A error that VLOOKUP throws when it can’t find a value, nest the IFERROR function to catch the error and return any value you like. In the case above it will return ‘Not found’:
 


= IFERROR(VLOOKUP(B2,Sheet2!B:C,2,0),"Not found")