Multiple Tiers by Member Question

[Mgarcia8878]

Sorry if this question has been asked before. I apologize as I am new to this community. I'm trying to create a workbook where I can grab the row of ranges that are specific to a member's calc. This would be similar to a vlookup of the Member's ID. In the example below, member XYZ has its own start and end ranges as well as it own rates. I'm currently using a SUMPRODUCT formula, however it is tied specifically to the row for that member. Is there a way to have the range driven by the Member's ID?


xyz's formula = SUMPRODUCT((B2<='Rate Tab'!F2:I2)* (B2>'Rate Tab'!B2:E2)* (B2- 'Rate Tab'!B2:E2)* 'Rate Tab'!J2:M2)+ SUMPRODUCT(((B2>'Rate Tab'!F2:I2)* ('Rate Tab'!F2:I2-'Rate Tab'!B2:E2))* 'Rate Tab'!J2:M2)
abc's formula = SUMPRODUCT((B3<='Rate Tab'!F3:I3)* (B3>'Rate Tab'!B3:E3)* (B3- 'Rate Tab'!B3:E3)* 'Rate Tab'!J3:M3)+ SUMPRODUCT(((B3>'Rate Tab'!F3:I3)* ('Rate Tab'!F3:I3-'Rate Tab'!B3:E3))* 'Rate Tab'!J3:M3)

Calc Sheet:

Rate Tab:

 

[Mgarcia8878]

Thank you Dave. I think this inspired me to just pull the ranges from the rate tab using a Vlookup. I think that is the simplest solution.

 

[Dave Patton]

"I think this inspired me to just pull the ranges from the rate tab using a Vlookup. I think that is the simplest solution."

Please show the formula that you decided to use.

N.B. We do not have full information on your data or your business requirements.
If you must use the Rate Tab even though there are 16384 columns available, there is another approach.

 

[Mgarcia8878]

Actually that was the solution I went with. No reason why I can’t v lookup the tiers back to the call sheet.