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

[willgarling]

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 ID	Date Sold	NET Price	Depletion 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 ID	Start Date	End Date	Tier 1 Selling Price	Tier 1 Depletion Allowance	Tier 2 Selling Price	Tier 2 Depletion Allowance	Tier 3 Selling Price	Tier 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

 

[jasonb75]

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
	A	B	C	D	E	F	G	H	I
1	Table 1
2	Product ID	Date Sold	NET Price	Depletion Allowance
3	157760	10/04/2020	$50.00	$16.70
4	881126	27/04/2020	$52.35	$0.00
5	881127	17/04/2020	$50.35	$7.65
6
7
8
9	Table 2
10	Product ID	Start Date	End Date	Tier 1 Selling Price	Tier 1 Depletion Allowance	Tier 2 Selling Price	Tier 2 Depletion Allowance	Tier 3 Selling Price	Tier 3 Depletion Allowance
11	157760	01/04/2020	31/12/2020	$63.75	$9.50	$62.85	$9.50	$52.35	$16.70
12	881126	01/04/2020	10/04/2020	$51.00	$7.65	$50.28	$7.65	$41.88	$10.09
13	881127	01/04/2020	31/12/2020	$51.00	$7.65	$50.28	$7.65	$41.88	$10.09
14	881137	01/04/2020	31/12/2020	$63.75	$9.50	$62.85	$9.50	$52.35	$16.70
15	881139	01/04/2020	31/12/2020	$51.00	$7.65	$50.28	$7.65	$41.88	$10.09
16	881140	01/04/2020	31/12/2020	$63.75	$9.50	$62.85	$9.50	$52.35	$16.70
Sheet5

Cell Formulas
Range	Formula
D3:D5	D3	=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]

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
	A	B	C	D	E	F	G	H	I
1	Table 1
2	Product ID	Date Sold	NET Price	Depletion Allowance
3	157760	10/04/2020	50	16.7
4	881126	27/04/2020	52.35	0
5	881127	17/04/2020	50.35	7.65
6
7
8
9	Table 2
10	Product ID	Start Date	End Date	Tier 1 Selling Price	Tier 1 Depletion Allowance	Tier 2 Selling Price	Tier 2 Depletion Allowance	Tier 3 Selling Price	Tier 3 Depletion Allowance
11	157760	1/04/2020	31/12/2020	63.75	9.5	62.85	9.5	52.35	16.7
12	881126	1/04/2020	10/04/2020	51	7.65	50.28	7.65	41.88	10.09
13	881127	1/04/2020	31/12/2020	51	7.65	50.28	7.65	41.88	10.09
14	881137	1/04/2020	31/12/2020	63.75	9.5	62.85	9.5	52.35	16.7
15	881139	1/04/2020	31/12/2020	51	7.65	50.28	7.65	41.88	10.09
16	881140	1/04/2020	31/12/2020	63.75	9.5	62.85	9.5	52.35	16.7
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=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)

 

[jasonb75]

... and that the OP has MAXIFS

The OP mentioned maxifs in the thread title...

It may be another assumption, but in my opinion it was a reasonably safe one.

 

[willgarling]

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.

 

[Peter_SSs]

I should probably update my user profile.

I see that you have done that now.

.. & jasonb75 was right on the money spotting it in the title (which I missed )