One of Excels most commonly needed Functions is the VLOOKUP. It is also possibly the function that most people have problems understanding. The Excel VLOOKUP function is used to look for specified data in the first column of a table of data. Once found it will return a result, on the same row, a specified number of columns from the first column. The syntax for VLOOKUP is:
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
It is used in the following manner:
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:
=VLOOKUP(A13,A2:B9,2,FALSE) or
=VLOOKUP(“Software”,A2:B9,2,FALSE)
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.
No comments:
Post a Comment