-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
What does this formula do?
- Thread starter azizrasul
- Start date
When in doubt, use the Evaluate Formula function to parse even the most complex formulas.
Given the number of conditions and the fact that it is passed to the MATCH formula, the SUMIFS is probably looking for a single result that meets multiple criteria (though it is summing if there are multiple lines that match all the conditions). I will operate under this assumption.
First, it returns the item in the column Export K which meet have the following values in Export columns B, H, E, and I as given (in order, the value of G4 [no way for me to know what this is], 1, Member, Individual). Think of this as a multi step lookup. Return the item in K for a specific row where a bunch of other conditions in other columns are met.
Then it looks for the item from K you just found within an array Current!Dental_Max (check your name manager to find the reference), and returns its position in the array from 1 to n (if it is the first item in the list 1, 2nd item in the list 2 and so on).
If it doesn't find that value, it returns an error which automatically returns the number 1
Given the number of conditions and the fact that it is passed to the MATCH formula, the SUMIFS is probably looking for a single result that meets multiple criteria (though it is summing if there are multiple lines that match all the conditions). I will operate under this assumption.
First, it returns the item in the column Export K which meet have the following values in Export columns B, H, E, and I as given (in order, the value of G4 [no way for me to know what this is], 1, Member, Individual). Think of this as a multi step lookup. Return the item in K for a specific row where a bunch of other conditions in other columns are met.
Then it looks for the item from K you just found within an array Current!Dental_Max (check your name manager to find the reference), and returns its position in the array from 1 to n (if it is the first item in the list 1, 2nd item in the list 2 and so on).
If it doesn't find that value, it returns an error which automatically returns the number 1
Upvote
0
@azizrasul.... Referring to the worksheet named 'Benefit Export', the formula sums (using SUMIFS) the values in column K where the corresponding row of column B equals G4 (in the worksheet that contains the formula) and the same row of column H equals 1 and the same row of column E equals "Member" and the same row of column I equals "Individual". If there is no row where all conditions are met, SUMIFS returns zero.
Then it looks up (using MATCH) that sum in the column identified by the named reference Dental_Max for the worksheet named 'Current'. If there is a match, the formula returns the relative row number of the match. If there is no match, the formula returns 1 (the last parameter of IFERROR).
You can click Formulas > Name Manager to see what column is referenced is by the name Dental_Max for the worksheet named 'Current'.
It is important to note that MATCH returns the relative row number in Dental_Max. If Dental_Max is a reference like J:J or J1:J10000, for example, the relative row number is the same as the worksheet row number. But if Dental_Max is a rerference like J10:J10000, for example, and MATCH returns 1, it refers to row 10, not row 1.
Then it looks up (using MATCH) that sum in the column identified by the named reference Dental_Max for the worksheet named 'Current'. If there is a match, the formula returns the relative row number of the match. If there is no match, the formula returns 1 (the last parameter of IFERROR).
You can click Formulas > Name Manager to see what column is referenced is by the name Dental_Max for the worksheet named 'Current'.
It is important to note that MATCH returns the relative row number in Dental_Max. If Dental_Max is a reference like J:J or J1:J10000, for example, the relative row number is the same as the worksheet row number. But if Dental_Max is a rerference like J10:J10000, for example, and MATCH returns 1, it refers to row 10, not row 1.
Upvote
0
Similar threads
- Question
- Question
