What does this formula do?

azizrasul

Well-known Member
Joined
Jul 7, 2003
Messages
1,304
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
=IFERROR(MATCH(SUMIFS('Benefit Export'!$K:$K,'Benefit Export'!$B:$B,G$4,'Benefit Export'!$H:$H,1,'Benefit Export'!$E:$E,"Member",'Benefit Export'!$I:$I,"Individual"),Current!Dental_Max,0),1)
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
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.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top