CODEWITHSUNDEEP
arraysexcelarray-formulas
Mor Sagmon
Mor Sagmon

Reputation: 1035

Approximate match within a sub-array

I have a table with the following:

Name   Quota-Date   Quota
Ami     5/1/2010    75000
Ami     1/1/2012    100000
Ami     6/1/2014    150000
John    8/1/2014    0
John    4/1/2015    50000
Rick    5/1/2011    100000

(Dates are shown in American format: m/d/yyyy). "Quota Date" is the first month of the active new "Quota" next to it. E.g. Ami's quota is 75000 for each month between May 2010 and December 2011.

I need a formula to fetch the quota of a given person and a given month: the active quota of a person in every month. This needed formula is to calculate the third column of this table:

Name    Month       Quota
Ami     6/1/2010    75000
Ami     12/1/2011   75000
Ami     1/1/2012    100000
Ami     7/1/2014    150000
John    10/1/2014   0
John    4/1/2015    50000

I prefer not to maintain the first table sorted, but if it will make things significantly simpler, I would.

What would be the correct formula for "Quota" on the second table?

Upvotes: 1

Views: 72

Answers (2)

Mor Sagmon
Mor Sagmon

Reputation: 1035

thanks, Máté Juhász! I just worked out another solution for that, not as an Array Formula, but I like your solution better - more elegant, I will use it!

My solution for the record:

=INDEX(INDIRECT("Quota!$E$" & MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers]) & ":$E$" & MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers])+COUNTIF(PMQuotaTable[PM],[@PM])-1),MATCH([@Month],INDIRECT("Quota!$D$" & MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers]) & ":$D$" & MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers])+COUNTIF(PMQuotaTable[PM],[@PM])-1),1))

I'm running a regular index/match with match type = 1 to find the largest date row, but I construct the target range dynamically to scope only the rows of the current person (PM). I identify the first row of this PM with this part: MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers])

...and the the last row for the PM by adding the number of rows he has in the table, so retrieved using this: MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers]) + COUNTIF(PMQuotaTable[PM],[@PM])-1

The dynamic range is then constructed using INDIRECT. So, the complete range is determined with this part (for the needed column to be retrieved eventually): INDIRECT("Quota!$E$" & MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers]) & ":$E$" & MATCH([@PM],PMQuotaTable[PM],0)+ROW(PMQuotaTable[#Headers])+COUNTIF(PMQuotaTable[PM],[@PM])-1)

Mor

Upvotes: 0

Máté Juhász
Máté Juhász

Reputation: 2327

If your new data is in columns A-C and original data is also columns A-C in Sheet1, then enter this formula in B2:

=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,MAX(IF((Sheet1!A:A=A2)*(Sheet1!B:B<=B2),Sheet1!B:B,"")))

This formula works well if you have only numbers in your 3rd column, but would be more complicated to make it working on text too.

Upvotes: 1

Related Questions