In this article you will look into the most used or should I say the most abused function in Excel. Yes, you guessed it right. It is the VLOOKUP function. So, we will look into VLOOKUP training.
Welcome to Excel VLOOKUP function.
VLOOKUP function training?
The VLOOKUP function is part of the family of lookups in Excel. The VLOOKUP function is used to lookup a value from the first column within a given range of cells in Excel and retrieve data from the specified column. The V in VLOOKUP stands for vertical, since the search happens in a first column of range of data .
The syntax of VLOOKUP along with the function arguments is as follows
=VLOOKUP(value to search for, table, column number, [range lookup])
- value to search for – refers to the value to be searched in the first column of the given range, This argument can be
- a literal value or
- a cell reference or
- a formula returning a value that can be used in the VLOOKUP function
- table – refers to the range/table to search in. Ensure that this data range has absolute reference. For example $A$1:$C$11
- column number – refers to the column number to retrieve data from
- range lookup – This argument is optional. Hence, I have used the [range lookup] in the syntax above.
- The value TRUE is used for approximate match. This is the default value that will be used if the fourth argument is blank.
- The value FALSE is used for an exact match.
Note: You have to ensure that the data type of the value to be searched is the same as the data type of the data in the first column of the table. What this means is if the search item is a text then the data type of the first column of the search range should also be text.
Examples of VLOOKUP function
The image of sample data that we will use for this article.
In the above table, the area in grey background is simulating the Excel layout, Where A/B/C refer to columns and 1-11 refer to the row numbers.
To practice the following formulas, copy the data in white background in a new Excel file. If you copy the above data in different rows and columns change the references in the below mentioned formulas accordingly
- Find out the Name of the person with the id A001
Type the following formula in say Cell E2
=VLOOKUP(“A001”, $A$2:$C$11, 2, FALSE), you get Ashok.
Here we have used the literal value of A001. Since its a string it is enclosed in double quotes.
We could also write the above formula using a cell reference for the first argument like this
=VLOOKUP(A2, $A$2:C$11, 2, FALSE)
- Find the age of the person named Nitin
=VLOOKUP(B11, $B$2:$C$11, 2,FALSE), you get 20.
- Find the Name of the person with the id “A020”
=VLOOKUP(“A020”, $A$2:$C$11, 2, FALSE), you get #N/A
The reason you get #N/A is because the id A020 was not found in the search range.
- Find the id for the person named Richa
=VLOOKUP(B3, $A$2:$B$11, 1, FALSE), you get #N/A.
The reason for this error is VLOOKUP cannot search to the left. The name is in the second column (column B) of the search range. And we are trying to return the value in first column (column A). This is not possible in VLOOKUP function.
The following image is showing the data and the formula examples. In this image Column E shows the formula results. Column F shows the formula used in column E.
Limitations of the VLOOKUP function
- VLOOKUP function always looks right. It cannot look towards the left.
- VLOOKUP function gets data based on the column number. So, if the data column is moved the results will be different.
To check this out add a new column before Name(Column B). Name it Education in B1. And give values like Graduate, Post Ggraduate, SSC, HSC to each of the cell from B2 to B11. And then see how the return value change for the formula used in example 1. As can be seen in cell F2 in the following image. The cell G2 shows what the formula in cell F2 looks like. As you can see the formula in G2 it is same as the one used inthe first example.
- VLOOKUP function works only when a column search is required. It will not work if a row search is required.
- VLOOKUP function is case sensitive.
Errors in VLOOKUP function
When using a VLOOKUP function, you are likely to run into the #N/A error. The #N/A error just means the lookup failed to get any data. The most common reasons for this type of error are
- The search value does not exist, is misspelled or contains an extra space
- The range lookup value is FALSE instead of TRUE
- The range is not entered correctly
- When copying VLOOKUP, range reference is not locked
- Mismatched data type of search value and the data type of the date in the first column.
VLOOKUP function Alternatives
Following functions are the alternatives for VLOOKUP function
- HLOOKUP function – The HLOOKUP function is used when a row search is required to be done. It is exactly the same as the VLOOKUP function. The only difference being HLOOKUP works on rows instead of columns
- INDEX+MATCH combo – The INDEX+MATCH function combo is the perfect replacement for the VLOOKUP function. The reason being it can search to the left, which is a limitation in the VLOOKUP function.
- MATCH function – The MATCH function returns the row number or column number of a search value based on the range used
- When the range used is a row, the column number is returned.
- When the range used is a column, the row number is returned.
- LOOKUP Function – The LOOKUP function searches a value in one column or row and returns a matching value from the same position in a different column or row.
We will be looking into the details of these functions in some other blog post dedicated to each of these functions.