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.
Insert a column to the right of the column you are using as the reference. This is where your formula and results will go. In the example below we are using the custName as our reference column, the VLOOKUP will be used to return the Country from a second sheet.
Type into your formula bar =VLOOKUP and complete the formula using the instructions below.
The formula means:
lookup_value – you define a value for the formula to look for by entering the cell here. In our example that’s custName.
table_array – the range of columns to look in, highlight the whole column where the reference is and drag over to where the value you want to be returned is. In our example that’s another sheet where we have the customer name, country and other fields recorded.
index_num – number of columns from the look_up value on our sheet to the column where the result is. If customer name is the first column and country is two columns over, enter the number 2.
range_lookup – this field indicated whether you are looking for an exact match, or a closest match. Generally you will want an exact match so enter 0 here.
When you hit ‘enter’ your value should be returned.
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.
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 this case, it will return ‘Not found’:
= IFERROR(VLOOKUP(B2,SHEET2!B:C,2,0),"NOT FOUND")