As we all know, VLOOKUP is the most useful function in excel. It is a simple yet very powerful excel function. The Excel VLOOKUP function lookup the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically(which explains the V in the function name). The syntax of VLOOKUP function is as mentioned below.
- lookup_value is the value to be looked up in the first column of the lookup table
- table_array: The range that contains the lookup table
- col_index_num: The column number within the table from which the matching value is returned.
- range_lookup: It is a optional argument. If is TRUE or ommited, an approximate match is returned.(if an exact match is not found, the next largest value that is less than lookup_value is returned.) If it is FALSE, VLOOKUP will search for an exact match. If VLOOKUP can't find exact match, the function returns #N/A.
Note: If the range_lookup argument is TRUE or ommited, the first column of the lookup table must be in ascending order. If lookup_value is smallest value in the first column of table_array, VLOOKUP returns #N/A. If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If the exact match is not found, the excel vlookup function returns #N/A.
VLOOKUP Function in Excel Tips And Tricks: As I mentioned in the starting VLOOKUP is a very powerful function in excel provided you are aware of how to use it. Here some of the tips which I came across with VLOOKUP which might be helpful for you to.
Using VLOOKUP function with wildcard charectors * and ?.
If your lookup_value argument is text and the range_lookup argument is False, the lookup_value can include wildcard charectors * and ?. This will be very useful when we need to use vlookup to show the income tax rates for various income levels or when we need to do lookup just first name from the table which has full name etc. Let us take a simple example. We have a data which has employee full name and employ id as shown below. We need to fetch the employee id by only the first name provided there is no duplicate of first name.
|Sample VLOOKUP data|
We have the value Anju in A8. We need to fetch the EMP id in B8. The the forumula would be =VLOOKUP("Anju*",A1:B5,2,FALSE). This VLOOKUP function returns employee id from the range which starts with Anju. If you want to use range with wildcard charector then, the formula will look like below.
Two way or Conditional VLOOKUP in Excel:
VLOOKUP function in excel searches for the first value in the lookup table. So what to do when we need to match both column and row fields? So we need touse two conditional vlookup or two way vlookup. The default formula does not provide option for this. However, with a simple trick you can achieve this. You need to use match function instead col_index_num as third argument. The example for the same is given below.
The match function function retunrs in the col_index_num instead of writing manually. It will be useful when you are working with huge data.
Handling errors in VLOOKUP excel function: When we use VLOOKUP function, at times we get error codes. To fix these error codes in vlookup function, we need to understand the error code and the meaning of it. There are three types of error code will be retuned. The first one is #N/A, second one #Value and third one #Name?. The second and third error codes we need to fix as it is a error in function itself. Below is the explanation for the same.
#Name?: The syntax of the formula is wrong. You need to recheck the formula and correct it.
#Value: The col_index_num is more than the range you have selected in the table. Correct the Col_index_num or increase range of the table.
#N//A: The lookup value not found. It is a common error. There is nothing wrong in the formula. You can get rid of this error code with desired value using the if function or iferror functions of excels.
Using VLOOKUP to looking up a value from multiple lookup tables: This can be done using a IF function. We use IF function to determine the table which you want to lookup for based on some condition.