As we all know, VLOOKUP is one of the most useful function in excel. It is simple yet very powerful excel function. The Excel VLOOKUP function, lookup for 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.
VLOOKUP in Excel is widely used for many purposes such as comparing two lists and finding duplicates, fetching the data from different list or page or creating template which updates based on the data on different sheet. Before we start exploring the tips and tricks on using VLOOKUP, lets understand the syntax and meaning of each arguments of VLOOKUP in excel.
- 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 in Excel – Tips And Tricks
VLOOKUP in Excel – Tips And Tricks
Using VLOOKUP function with wildcard characters * and ?.
If your lookup_value argument is text and the range_lookup argument is False, then lookup_value can include wildcard characters * 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.
We have the value Anju in A8. We need to fetch the EMP id in B8. The formula 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 character 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 to use 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 returns 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, we get error codes if the lookup value is not there in the lookup table or syntax error. 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 returned. 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.