VLOOKUP formula matches a string against the 1st column of a range and returns any cell value from the matched row.
VLOOKUP Formula Syntax
VLOOKUP Formula has three parts:
VLOOKUP (value_to_find, range_to_search_in, column_number_to_return, match_type)
value_to_find is the value that we would like to find. You can specify a string, a number or a cell address as the value you would like to search for. While using a string enclose it within quotes (Ex. "Apple"). When searching for a cell provide the address of the cell (Ex. B12). Numbers can be entered as such. (Ex. 19)
The range_to_search_in is the range in which we would like to search for the value and return the match. When you specify this range, the VLOOKUP only searches the first column of the range (and NOT all the columns) for the value which we would like to find.
match_type is specified as either TRUE or FALSE. If this value is specified as FALSE (or false in small-case), the VLOOKUP function will find and exact match. If it does not find a match, it will return an error value. However if this value is specified as TRUE, then the VLOOKUP function tries to find and exact match but if it does not, the next largest value that is less than lookup_value is returned. This parameter is optional and if not specified is taken a TRUE by default. (To make this work properly, the values in the column that we want to return from the function have to in an ascending order – but you can ignore that for now. For most cases, a match_type value of FALSE is all that we will ever need.)
Example of a VLOOKUP Formula
Let’s look at an example of the VLOOKUP formula. Suppose we had a table as shown in the above example. We have a list of products in the first column and their sales values in the second. Suppose you wanted to know how much were the sales for the product “Software”. We can write the VLOOKUP formula as:
Both of the above formulas would return a value of 10. How does it work ? Let’s look at each part of the formula carefully. The first part of the formula, as we saw earlier, is the value that we would like to find. In our case, we can either specify the cell (A13) or the string (“Software”) that we would like to find. The second part is the range in which we would like to search which in our case is A2:B9 (the table in which we have stored the values). The third part is the column that we would like to return a value from in case the value we were trying to find was found in a particular row. Specifying a value of 1 would return the first column (which was also the column we searched against). A column value of 2 would return the figures from the second column (which is what we want in our example).
How to enter the VLOOKUP formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =VLOOKUP(
3. Move the cursor using the up-down or left-right arrow keys and take it to the cell which contains the value that you are trying to find.
4. Press the comma key (,)
5. Again move the cursor using the up-down or left-right arrow keys and take it to the first cell (top-left) of the range that you would like to search.
6. Now keeping the SHIFT key pressed, move the cursor again and take it to the last cell (bottom-right) of the range that you would like to search.
7. Press the comma key (,) again
8. Type in the column number from which you would like to return the value from. (If you don’t get it at first, simply type in 1).
9. Press the comma key (,) again
10. Type in FALSE and then close the formula bracket by typing in ).
(Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =VLOOKUP(“Software”,A2:B9,2,FALSE) )
Possible Errors with the VLOOKUP Formula
The VLOOKUP formula can result in the following error values:
VLOOKUP #N/A Error
The #N/A Error value in VLOOKUP is one of the most frequently occurring error value. This signifies that the value that you are trying to find does not exist in the range in which you are trying to find it. If you get this error, try going back and check whether the value that you are trying to find exists and is in the first column of the range in which you are trying to find it. Carefully check the range that you’ve specified. Foe example, if we were to specify =VLOOKUP(“Cake”,A2:B9,2,FALSE) in the above example, it would result in an #N/A error value simply because the value “Cake” does not exist in the first column of range in which we are trying to find it.
VLOOKUP #REF! Error
#REF! error in VLOOKUP specifies that you have specified a match to be returned from a column that does not exist in the range in which you are trying to find the value. For example if we were to write the formula as =VLOOKUP(“Software”,A2:B9,33,FALSE), it would give us the #REF! value because we have specified that if there’s is a match, it should return the 33rd column from the range. However the range that we have specified (A2:B9) has only two columns (A and B) and hence trying to reference the 33rd column results in an error.
VLOOKUP #NAME? Error
#NAME? Error in VLOOKUP can result from wrongly specifying address the range in which to find the value. Say for example you wanted to write =VLOOKUP(“software”,A1:B9,2,FALSE) but instead erroneously entered =VLOOKUP(“software”,A1:BBB9,2,FALSE) (two extra B’s). Now the cell BBB9 does not exist anywhere in the sheet and as a result the VLOOKUP function throws up the #NAME? error value.
You can know more about hiding errors in a worksheet here.
You can download an example of VLOOKUP formula here or click on the button below: