Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

How to create a formula with multiple Excel functions

How to nested a function within another function in Excel and make a formula

 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.

How to set up a formula with multiple functions in Excel

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:  
VLOOKUP (lookup_value, table_array; col_index_num; range_lookup)


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.

How to display an Excel function arguments

  • 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:  
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 a 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 Function MATCH function within VLOOKUP

Now, when I explained the functions VLOOKUP and MATCH 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?

How to insert the Match function in the VLOOKUP function and create a formula in Excel
If we use only one function and that targeted because we know the result of the formula would look like this

=VLOOKUP(F2;$A$2:$D$6;3;0)

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'. The 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 the 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 functions Match, 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.

=MATCH(G2;$A$1:$D$1;0)

From the formula we see that the function Match 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 function VLOOKUP 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: 
=VLOOKUP(F2;$A$2:$D$6;MATCH(G2;$A$1:$D$1;0);0)
If you name the range of data then the formula might look like this: 
=VLOOKUP(F2;data;MATCH(G2;fruits;0);0)

Combining Excel functions INDEX and MATCH

This task can be solved by combining other functions such as these formulas below.

=INDEX($B$2:$D$6;MATCH(F2;$A$2:$A$6;0);MATCH(G2;$B$1:$D$1;0))

The first function MATCH 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, 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 formula that returns the same results but with a different combination of OFFSET and MATCH functions.

=OFFSET($A$1;MATCH(F2;$A$2:$A$6;0);MATCH(G2;$B$1:$D$1;0))

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.

=HLOOKUP(G2;$A$1:$D$6;MATCH(F2;$A$2:$A$6)+1;FALSE)

I hope that I managed to beginners in Excel explain how they can insert an Excel function to another Excel function 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
  1. Select the cell containing the formula with a nested function
  2. In the formula bar, click the mouse after the first argument VLOOKUP function (ie, place the cursor in the first argument).
  3. 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).
  4. You notice that it is now marked Excel Match function that I nestled.
  5. Press F9

How you can quickly know result of nested Excel function

After you press F9, you note that Excel automatically calculates the result function Match and display it in the formula bar. Excel displays the number "3" (picture below), this number is the number of the column from which we want to display the result of VLOOKUP formula.

How to quickly display result of the nested function instead Excel Evaluate Formula