Find column within a row using index and maxifs.. or match

willgarling

New Member
Joined
Jul 5, 2011
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Okay forum, I have been racking my brain and cannot figure this one out. I need to find the tier depletion allowance from table 2 for the tier selling price (table 2) that is closest without going over the actual selling price in table 1. Table 1 shows the product ID, the date sold, the selling price, and the depletion allowance for that price. I filled in the depletion allowance field with the value I need returned from table 2. Table 2 has the product ID, start and end date, and then columns showing tier selling prices and their corresponding tier depletion allowances. The date sold in table 1 must be in within the start and end dates in the corresponding product in table 2.

For product 157760, the selling price was $50.00. The closest tier selling price without going over from Table 2 is tier 3 selling price $52.35. so the depletion allowance is tier 3 16.70.
For product 881126, the date cold is 4/27/20. That date is outside the start and end dates for 881126 in table 2, so the depletion allowance is $0.00
For product 881127, the selling price is $50.35. The closest tier selling price without going over from table 2 is tier 1 $51.00, so the depletion allowance is tier 1 $7.65



Table 1
Product IDDate SoldNET PriceDepletion Allowance
157760​
4/10/2020​
$ 50.00$ 16.70
881126​
4/27/2020​
$ 52.35$ 0.00
881127​
4/17/2020​
$ 50.35$ 7.65



Table 2
Product IDStart DateEnd DateTier 1 Selling PriceTier 1 Depletion AllowanceTier 2 Selling PriceTier 2 Depletion AllowanceTier 3 Selling PriceTier 3 Depletion Allowance
157760​
4/1/2020​
12/31/2020​
$ 63.75$ 9.50$ 62.85$ 9.50$ 52.35$ 16.70
881126​
4/1/2020​
4/10/2020​
$ 51.00$ 7.65$ 50.28$ 7.65$ 41.88$ 10.09
881127​
4/1/2020​
12/31/2020​
$ 51.00$ 7.65$ 50.28$ 7.65$ 41.88$ 10.09
881137​
4/1/2020​
12/31/2020​
$ 63.75$ 9.50$ 62.85$ 9.50$ 52.35$ 16.70
881139​
4/1/2020​
12/31/2020​
$ 51.00$ 7.65$ 50.28$ 7.65$ 41.88$ 10.09
881140​
4/1/2020​
12/31/2020​
$ 63.75$ 9.50$ 62.85$ 9.50$ 52.35$ 16.70
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,419
Office Version
  1. 365
Platform
  1. Windows
This method is based on the assumptions that tier 2 selling price will never be higher than tier 1 and tier 2 depletion will never be lower than tier 1 (same for tier 3 and 2).
Also that for any individual product, the highest depletion will never be more than the lowest selling price.
Book1
ABCDEFGHI
1Table 1
2Product IDDate SoldNET PriceDepletion Allowance
315776010/04/2020$50.00$16.70
488112627/04/2020$52.35$0.00
588112717/04/2020$50.35$7.65
6
7
8
9Table 2
10Product IDStart DateEnd DateTier 1 Selling PriceTier 1 Depletion AllowanceTier 2 Selling PriceTier 2 Depletion AllowanceTier 3 Selling PriceTier 3 Depletion Allowance
1115776001/04/202031/12/2020$63.75$9.50$62.85$9.50$52.35$16.70
1288112601/04/202010/04/2020$51.00$7.65$50.28$7.65$41.88$10.09
1388112701/04/202031/12/2020$51.00$7.65$50.28$7.65$41.88$10.09
1488113701/04/202031/12/2020$63.75$9.50$62.85$9.50$52.35$16.70
1588113901/04/202031/12/2020$51.00$7.65$50.28$7.65$41.88$10.09
1688114001/04/202031/12/2020$63.75$9.50$62.85$9.50$52.35$16.70
Sheet5
Cell Formulas
RangeFormula
D3:D5D3=IF(B3=MEDIAN(B3,INDEX($B$11:$C$16,MATCH(A3,$A$11:$A$16,0),0)),MAXIFS(INDEX($E$11:$I$16,MATCH(A3,$A$11:$A$16,0),0),INDEX($D$11:$H$16,MATCH(A3,$A$11:$A$16,0),0),">="&C3),0)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
This method is based on the assumptions ...
... and that the OP has MAXIFS which I believe only exists in Microsoft 365 and excel 2019, not the OP's Excel 2016.

I'm not certain that I have the logic right but I do also match the 3 sample results, with a few less of the assumptions required.

willgarling 2020-04-29 1.xlsm
ABCDEFGHI
1Table 1
2Product IDDate SoldNET PriceDepletion Allowance
315776010/04/20205016.7
488112627/04/202052.350
588112717/04/202050.357.65
6
7
8
9Table 2
10Product IDStart DateEnd DateTier 1 Selling PriceTier 1 Depletion AllowanceTier 2 Selling PriceTier 2 Depletion AllowanceTier 3 Selling PriceTier 3 Depletion Allowance
111577601/04/202031/12/202063.759.562.859.552.3516.7
128811261/04/202010/04/2020517.6550.287.6541.8810.09
138811271/04/202031/12/2020517.6550.287.6541.8810.09
148811371/04/202031/12/202063.759.562.859.552.3516.7
158811391/04/202031/12/2020517.6550.287.6541.8810.09
168811401/04/202031/12/202063.759.562.859.552.3516.7
Sheet1
Cell Formulas
RangeFormula
D3:D5D3=IFERROR(INDEX(D$11:I$16,MATCH(A3,A$11:A$16,0),AGGREGATE(14,6,(COLUMN(D$11:I$16)-COLUMN($D$11)+1)/((RIGHT(D$10:I$10,5)="Price")*(A$11:A$16=A3)*(B$11:B$16<=B3)*(C$11:C$16>=B3)*(D$11:I$16>=C3)),1)+1),0)
 

willgarling

New Member
Joined
Jul 5, 2011
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Thank you guys!! I do have 365, so the first formula worked... I should probably update my user profile. Thanks again I really appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,192
Messages
5,623,300
Members
415,964
Latest member
psandvig

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
Top