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.
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.
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.
Usually, Vlookup compares two values and return the value next to the matched value. But in this example, we are checking whether the two values are matching and then return a formula. This formula will then execute in the cell where we write the Vlookup function. You can copy down the formula and can have different formulas according to the values in the lookup. Example: Open the file Chapter 3.4 - Vlookuplf.xlsx Here we have two regions East and West mentioned in B column and the salesperson names in A column. Now in the result column if the region is East we have to calculate Qnty * 9/75 and if it is West you have to calculate Qnty * 8/85. [example] This can be done easily with the help of If function. Enter the formula =IF(B2="East",C2*9/75,C2*8/85) in D2 cell and copy down. If the region mentioned in B column is East, it will execute the formula C2*9/75, and if it is not east, it will execute the formula C2*8/85. Now if there are more regions (say more than ten) and each region has separate formula then it will be difficult to write, edit and maintain the IF formula. In this case, you can combine Vlookup and Evaluate function to get this result very easily. EVALUATE is an Excel v4.0 macro function still supported in Excel. The EVALUATE function evaluates the text equation as an algebraic equation. But Evaluate cannot be directly used in a cell like SUM or COUNT function. If you use Evaluate like this EVALUATE(B1), you will get an error message, That function is not Valid. EVALUATE function can only be used with NAMED RANGE. This is an embedded Excel macro function and for this function to work, you must save the file as an Excel Macro-Enabled Workbook (.XLSM) file. We will go through this example for you to understand. Example: Open the file Chapter 3.5 - VlookupEvaluate.xlsx Now save this file as.XLSM file and go through these steps. 1. Select any cell inside the data and use the keyboard shortcut CTRL + T, a popup will ask for the range and you have to click OK. Entire data will be converted to Table. 2. Now you will get a new Tab called Design on the Excel when you select the Table. Rename the Table name as Sales from the Design tab (you can give your own name but right now we will stick with Sales). By converting the data to Table, you can multiply or subtract the Qnty column with the header name instead of the cell reference. For example, if you want to multiply the Qnty column by 10. You can write like this =Sales[Qnty]*10 instead of =C2*10, means go to table Sales and find the heading column Qnty and multiply with ten. This is called Structured Referencing and the main advantage is you can see what the formula is doing with what data because the cells are not referred as C2 or B2. Just by looking at the formula you can see which columns are used for multiplying. In other words, you can concentrate on the data instead of the cell reference. 3. Next, we have to create the formulas for East and West region. First, put the region name East in F2 and formula Sales[@Qnty]*9/75 in the adjacent G2 column. Then enter West below East and the corresponding formula Sales[@Qnty]*8/85 in G3 column. You can write these on same sheet or a separate sheet. This is for comparing the region using the Vlookup and getting the corresponding formulas. One important thing is you should not put the equal sign before the formula. The first part of the formula is Sales, the table name you have given. Square brackets are used for referring to the individual columns inside the table; here Qnty is the third column in the table. The ampersand character (@) is used to identify “This Row" in a structured reference. This will calculate the formula for each row separately when you copy the formula. And the last part is the calculation we use on the Qnty column. 4. Next, we have to click Define name in the Formulas tab or by using the shortcut CTRL+F3 and then click new. In the define name, you can give a name. Here I have given the name Calc, and in the Refers to section you can enter the formula =EVALUATE(VLOOKUP(Sheet1!B2,Sheet1!$F$2:$G$3,2,0))+0*TODAY(). You have to either type the formula or copy the formula. Be cautious when you type the formula if you click in any other cell that cell will also be considered in the formula. If the formula you have entered has gone wrong or you want to see the formula go to Name Manger in the Formulas tab and select the name Calc. If you want to edit the formula make changes in the refers to section and click close. You will be prompted to save the equation and you can save if you want to keep the changes. 5. Now you can use the formula we have defined. In cell D2, use the Name Calc you have just created. Put an equal sign and then select Calc from the drop down list. Result column will be automatically get filled till the end of the table. What this Vlookup formula does is it will compare the region East and West against the one mentioned in the F column and get the formula from G column. Evaluate will convert the text to formula received from the Vlookup function. And the last part 0 * Today() is used to refresh the result part when any Qnty changes or any new row is added. Today function returns the current system date. It is a volatile function that recalculates the formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether the precedent data and formulas on which the formula depends have changed, or whether the formula also contains non-volatile functions. We have multiplied Today with O to make the last part zero. So it will not make any changes to the actual formula. The final file will look like this. It is saved in the Reference folder under the name Chapter 3.6 - VlookupEvaluate xlsm [example]