vlookup.co
Learn VLOOKUP HLOOKUP and INDEX MATCH In-depth

# Other VLOOKUP uses

01 Mathematical equations as lookup value

We can use a mathematical equation as a lookup value. Example: Open the file Chapter 3.1 - Vlookup MathEquation.xlsx Here we have some numbers in A column and the words in B Column. Now I have other numbers in D and E column. See the image below. [example] Now instead of using the cell reference directly you can subtract the first number from the second number and use that number as lookup value. Then you can use this lookup value to fetch the data from B column. This example is to show how we can use Vlookup in various ways. Enter the formula =VLOOKUP(D2-E2,\$A\$2:\$B\$5,2,0) in F2 cell. In the first parameter, we are subtracting 100 from 2. We will get 98 and this is used as the lookup value. Instead of subtraction in the first parameter, you can also use division or multiplication or whatever equation you want according to the situation.

01 Naming first and second parameter

In the Vlookup formula, the first two parameters are cell references. Instead of cell references, you can give descriptive names to the cells and use them as parameters. So it will be easy to know what each parameter is holding just by looking at the names. For example, if we write a Vlookup formula like this Vlookup(Name,BonusData,2,0) you will get some idea what first two parameters are holding. Example: Open the file Chapter 3.2 - VlookupName.xlsx This is the same example we have used in the first chapter. Here we have the names in A column and the person qualified for the bonus and their bonus amount in D and E column. [example] Before writing the formula, we have to name the columns. For naming, first select A column and then click the name box above the A column( you can see the cell reference A1 if you selected A column). Then delete the cell reference in the name box and type Name. Now do the same for D and E column. Select both the columns and then rename it as BonusData. Now select A column and check the name Name is showing up in the name box. Do the same by selecting D and E column. Ok, now you can write the Vlookup formula VLOOKUP(Name,BonusData,2,0) in B2 cell and copy the cells down. As I have stated earlier, it is easy to read this formula and easy to understand what each parameter is holding. Just by looking at the first two parameters you can easily understand what you are searching and where you are searching. Another advantage is all the new names added in the A column in the future will be detected automatically in the Vlookup formula. This is because we have selected the entire A column and named as Name. The same is true for D and E column; we can easily add more data like names and bonus because we have selected the entire column. Instead of naming the entire column you can name the data cell range, or if you are sure some data additions will not happen beyond a number. For example, you can name A2:A1000 as Name and D2:E1000 as BonusData to cover any additions to the data later. This will reduce the array size and will search till thousand row.

01 Vlookup without any unique values

We have just gone through an example where we have used the first name to fetch the bonus data of ten employees. But in a big organization, there will be hundreds of employees with same first name and different surnames. For example, in an organization, there are two employees with the name James with a different surname. And in Excel, the person qualified for the bonus is mentioned some rows below the first James. Then Vlookup will fetch the first person if you are searching with only first name and the result will be wrong. In this case, you have to use the second name also in the Vlookup formula. Example: Open the file Chapter 3.3 - Vlookup Unique.xlsx Here we have first and second names of the employees mentioned in A and B column. In that, we have two employees with the name James as the first name and two names with Ken as the first name. If you do the Vlookup with the first name, then you will get the wrong answer. Go through the image given below. [example] Even though their first names are same, their second names are different. So what we can do is we can concatenate the first name and second name mentioned in E and F column. Enter the formula = E2&F2 in D2 column and then copy down the formula. You will get the first and the second name clubbed together in D column. Now you can do the Vlookup easily. Refer to the image after joining. [example] Enter the formula =VLOOKUP(A2&B2,\$D\$2:\$G\$4,4,0) in C2 cell and copy it down. The first parameter we have used the & operator to concatenate first name and second name. Then we have selected the table array from the new helper column created in the D column as second parameter and completed the formula. Now you will get the correct answer. What if the organization has more than one person with the same first and second name? In this case, you can concatenate employee id or any other unique identity to make it unique. You can add the Employee id or any other unique numbers to make the lookup value unique. You have to add the same in both lookup value as well as the table arrays first column. Before writing the Vlookup, you have to check for duplicate names of the employees after joining the employee names. For this first, you should join the two names mentioned in A and B column with concatenation operator. After that, you can check duplicates using Conditional Formatting. It will highlight all the duplicate values with the color you specify. If there are no duplicate valu can skip joining the employee id or any other values and instead joint first and second name only.

01 Vlookup to return formulas instead of values