VLOOKUP functions of excel is very useful when you need to return a value from a table or a range of cells by looking up another value. VLOOKUP on excel can be used for finding the value in a range which are present in the other range in the same sheet or other sheet and other excel files. Let is quickly look into the syntax of vlookup function and examples on how to use VLOOKUP on Excel. Do note than simple VLOOKUP function works with only single criteria. If you are looking for 2 or more criteria vlookup, the please refer my previous post multi criteria vlookup.
Syntax of Excel VLOOKUP Function:
= VLOOKUP( value, table_array, index_number, Typeoflookup )
Value: is the value to search for in the first column of the table_array or range of cells. Value can either a text or a numerical value.
table_array: is the range of cells where you need to lookup for the value.
index_number: is the column number in table_array from which the matching value must be returned. A index_number argument of 1 returns the value in the first column in table_array; a index_number of 2 returns the value in the second column in table_array, and so on.
Typeoflookup: This is not mandatory. However this will chose between exact match and relative match. If you use TRUE or if you omit this argument, then non exact match lookup will be performed. If this is set to FALSE, then vlookup does the exact match
Note and Troubleshooting Tips on Excel:
- If “Typeoflookup” is either TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
- If “Typeoflookup” is FALSE, the values in the first column of table_array do not need to be sorted.
- Excel vlookup returns #VALUE! if “index_number” is less than 1 and returns #REF! if the “index_number”Greater than the number of columns in table_array. Also one more error value comes with VLOOKUP is #N/A. Excel returns #N/A when there is no exact match or the value you are matching in the range is not found.
- If there are two or more values in the first column of table_array that match the lookup_value, the first value found will be returned
Example on Using VLOOKUP formula in Excel:
Using vlookup with Exact match:
Considering above sample data, if you need to lookup for the value “Banana” and fetch the Rating, use the formula given below.
=VLOOKUP(“Banana”,A2:C6,3,FALSE) which returns C.
Using vlookup with non Exact match:
This is useful, when you need relative value. Say for example, your matching value based on the “% of commission” and you want to return the ratings. Here do not that, for using non exact match the first column of lookup range must be placed in ascending sort order.
=VLOOKUP(33%,B2:C6,2,TRUE) which returns C. Here first instance of nearby value is taken by the formula.
Formatting the VLOOKUP on Excel using ISNA and IF functions:
When there is no match found, excel returns #n/a. This is will give problems when you need to use SUM function on the return value of returned value.To overcome this, we need to use ISERROR function. This very useful, if you are creating a template with VLOOKUP function.
From the same above sample table, we will match Strawberry. The formula returns #n/a as the match is not found on the table.
=IFERROR(VLOOKUP(“Strawberry”,A2:C6,3,FALSE),”Not Found”) which returns “Not found”.
The IFERROR functions syntax is =IFERROR(Value, value if error) where “value” is the data which needs to checked for error and “value for error” is the value to be returned incase of error.
In above example we use VLOOKUP return data as value and “Not Found” used if the returned value is #N/A.
IFERROR function was introduced in Excel 2007. Hence if you are using earlier version of Excel 2007, then you need to use IF function and ISERROR function of Excel. So the formula is as shown below.
Note: Do not use this function unless you have earlier versions of excel as this will lead to calculation time. This is because for every time you use the function, excel needs to calculate vlookup twice. when range to lookup is more and number of instances of using this formula more, then excel will take really long time to calculate and return the right values.
Please do share your feedback and tips which you know in the comment section below. If you need any specific support on Excel, then mention your query in comment section, I will try to answer your query.
Video Tutorial for VLOOKUP