As we all know, VLOOKUP Function looks for some value into a data-table and returns a corresponding value that we a looking for. But what if we are not looking for the value but the formula used in the corresponding cell?
Yesterday one of our readers, Faisal asked the same question:
Can you help me by explaining how we can copy a particular formula in a cell by using vlookup for a common cell in 2 different excel files?
E.g. – I have a data as Column 1 – Site No, Column 2-Qty, Column 3-Rate, Column 4-Amt (Qty x Rate).
Now I have another excel file with Site no. common for vlookup and even other columns but I just want formula of column 4, to be picked up. Only the formula, how it can be done?
By default, VLOOKUP function will always return a value and not the formula. What Faisal is looking for can be understood with the following example.
In Table – 1, we have site, quantity, rate and amount. The amount is calculated based on site. For example, for Delhi the amount is calculated by multiplying quantity and rate but for Mumbai, there is a discount of 10% in the amount calculated. Similarly, for Kolkata, we have a flat discount of $10 in the amount.
In Table – 2, we are interested in getting the amounts based on the formula used in Table – 1. Table – 1 and Table – 2 are located in different sheets.
If we just use the VLOOKUP formula, all we get is the value from Table – 1. For Kolkata, VLOOKUP returns the value 630 where as we are interested in getting 190 (5*40-10).
Solution to this VLOOKUP Problem
First thing we need to do is to get the formula used for calculating the amount in a separate column. Excel allows you to view the formula used – You can toggle between the values and formulas by using (Ctrl + `) shortcut (` Key is located just at the left side of 1 in keyboard).
But unfortunately there is no built-in function in excel to get the formula used in a separate column. We need to define a custom function (macro) for this functionality.
How to add Custom Formula
- Press Alt+F11 to open the Microsoft Visual Basic Editor.
- Go to Insert -> Module
- Here, we will define a custom function to fetch the formula used in any cell. Don’t worry; it is just a few lines of codes which you can easily understand.
- Type the following
get_formula = cell.Formula
- Click on “Save” icon. You have just defined a custom function in excel.
Since our custom function is ready, we will use this to get the formula used in “amount” column in Table – 1. Insert a new cell and put a heading “Formula” and use “=get_formula(D3)” in column E3 . Just like other excel functions, you can drag it to fill the formula used for other rows.
Great! Now since we have the formula ready in Table – 1, we can use VLOOKUP to get the same in Table – 2 in different sheet as shown below.
Copy the formulas we have just got with VLOOKUP and paste it as values in a new column “Formula Paste” (right click -> paste special -> values).
We get the formulas in text here; however, we are two steps behind what we are looking for.
In first step we need to change the numbers in formulas according to the placement of sites in this sheet. E.g. for Kolkata, we want to change the formula “=B5*C5-10” to “=B3*C3-10”. In second step, we are interested to get the formula result.
Changing the Numbers to suit the location of Sites
- Add one more column “Row-Num” in both Table – 1 and Table – 2. Put the row numbers against each row.
- In Table – 2, add one more column “Original Row-Num” and get the corresponding row number from Table – 1 against each site. We will of-course use VLOOKUP for this.
- Now we will use “SUBSTITUTE” function to replace Original Row Numbers with Current Row Numbers from Formulas.
- In “amount” column, use “=SUBSTITUTE(F3,H3,G3)”. Drag for rest of the rows.
- Select all the resultant formulas in the “amount” column. Copy and paste special as values. Now go to “Data” tab -> “Data Tools” -> Text to Columns.
- Click Finish and you are done!
Well, there are several steps involved but it is not difficult to understand it. Mainly, we are doing two things – first extracting the formula used and then changing the reference numbers to get the formula results.
In case you are looking for the excel sheet used in this example, you can download it from the below link.
Do you know some alternate method to get the same results? If yes, share it with your comments below.