How to nested a function within another function in Excel and make a formula
I believe that you know what the formula is and how it is Formula created in Excel, if you do not know, see the tutorial at the link Create formula in Excel. In this example tutorial I show you how you can embed an Excel MATCH function within the VLOOKUP function.
The situation in the picture below you see a task that must be solved. In Excel cell H1 looking result from a range of data A1:D6 based on two conditions that are found in cells F2 and G2.
This problem of searching and restoring order and column intersection can be solved Vlookup function. I believe that you know the syntax of Excel VLOOKUP function to search a range of data.
Excel VLOOKUP function
VLOOKUP function has the following syntax:
In the syntax we can see the function arguments to be set up to function returned the desired result. Arguments Excel functions you can see when you start typing a formula, and if you want the text to print the arguments of the function, press CTRL+SHIFT+A after you type the function name and the opening parenthesis. The described method looks like the image below.
- The first argument: lookup_value condition for which we want to return a specific result, in this case to find row in which the condition and return the result from column to another condition or requirement
- The second argument: table_array a range of cells in which the search terms and results and it's range A2:D6
- The third argument: col_index the column number from which we need to score and it determines the condition 2 that is the name of the fruit
- The fourth argument: range_lookup logical value TRUE or FALSE depends on whether we want to find the exact value or an approximate
Excel MATCH function
Match function has the following syntax: This function we will neste instead of 3rd argument of Vlookup function. (col_index_num)
=MATCH (lookup_value; lookup_array; match_type)
In the syntax of this function you will see function arguments that need to be set to function returned the desired result. In this case (you will see later), this function returns an number column. Otherwise, the function MATCH may return the number of rows in a single column (all depends on which combination is used).
- The first argument: lookup_value condition for which we want to return a specific result, in this case, find the column number for the set of conditions or criteria
- The second argument: table_array a range of cells in which the search terms and the results, note that the first order of the names in the first row of fruit
- The third argument: match_type is the type that can be -1, 0 and 1 (more on the types of the link function MATCH)
Insert MATCH function within VLOOKUP
Now, when I explained the VLOOKUP and MATCH functions we can start to create a formula with inserted Excel function inside the other major Excel function. The result in the cell for given conditions the value of 110 as we see from the data range. How did we get this result?
If we use only one function and that targeted because we know the result of the formula would look like this
So, we know that our result for the terms 'Mark' and 'Plum' value of 110, which is located in Column 3. VLOOKUP the first condition is found in Row 4 and the second condition in Column 3 (Excel counts the columns from left to right; A, B, C, D and so on. In this case, Column C is the third column in a row. However, what would happen if we enter the second condition 'Apricot'. Formula in this form back to the same result as in the first case under the conditions of 'Plum'. why is this so? Since we have a formula third argument is still the number 3 that is the column number.
To avoid these problems it is necessary to automate the column number with respect to the condition 2. This will solve with the MATCH function. Excel Match functions, will automatically return the column number relative to the set condition of the second
How does the Match function in this case. Method of calculation function, step by step, you can see using the command 'Evaluate Formula' in the group commands 'Auditing' on 'Formulas' toolbar. Thus, the MATCH function returns as a result of the ordinal number of the column of the specified range of data in this case is the range in the first row A1:D1.
From the formula we see that the Match function condition 2 in cell F2 requested data range A1:D1. When the function finds an identical value (indicated by the argument 'match_type') will return the number of columns in which the identical value. In this case the data in column number 3 in the first row. If the 'condition 2' set the name of fruits 'Apricot' then this formula and function returned a column 4 because it is identical to the value/text condition 2.
Now, we come to the crucial decision in order nested an Excel MATCH function within Excel VLOOKUP function. I hope you realize that the third argument of the VLOOKUP function is the column number. Given that the MATCH function as the result returns the number of columns, instead of the number in the VLOOKUP function for the third argument (col_index_num) we can set the MATCH function.
And at the end of the formula looks like this:
If you name the range of data then the formula might look like this:
Combining Excel functions INDEX and MATCH
This task can be solved by combining other functions such as these formulas below.
The first MATCH function in the formula above returns the number 3 for condition1 in column A2:A6. In this case Row4 the third row in the series range data.
Another function of the MATCH returns the number 2 for 'condition 2' which indicates the number of columns for the range B1: D1. In this case, the column C is the second column in the series.
Finally INDEX function which has the following syntax =INDEX (array, row_num, column_num) finds the intersection of 'Row 3' and 'Column 2' of the data range B2:D6
Combining Excel function OFFSET and MATCH
Another example formula that returns the same results but with a different combination of OFFSET and MATCH functions.
So, the formula above returns the same result as all the above integral formula but by OFFSET function calculates the shift away from said first argument. This formula to study leave to you, as this HLOOKUP function formula below, which also returns the same result.
I hope that I managed to beginners in Excel explain how they can insert an Excel function to another Excel function Examples in place of a function argument.
How quickly find which is the result of the nested function
Except "Evaluate Formula" if you want, in the formula bar you can find out the result of Excel functions that you nestled inside the other Excel functions. In this example I will show how quickly you can find out the result of the Excel MATCH function, which is nested in the Excel VLOOKUP function without having to browse Evaluate Formula.
In the picture below, see a situation in which I nestled Excel MATCH function inside Excel VLOOKUP functions. To quickly view the result of the MATCH function do the following
- Select the cell containing the formula with a nested function
- In the formula bar, click the mouse after the first argument VLOOKUP function (ie, place the cursor in the first argument).
- Now, you showed up box that displays all the arguments VLOOKUP functions. Within this box, click on the third argument of the VLOOKUP function (col_index_num).
- You notice that it is now marked Excel Match function that I nestled.
- Press F9 key
After you press F9 key on keyboard, you note that Excel automatically calculates the result Match function and display it in the formula bar. Excel display the number "3" (picture below), this number is the number of column from which we want to display the result of VLOOKUP formula.