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

#### willgarling

##### New Member
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### jasonb75

##### Well-known Member
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
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)

#### jasonb75

##### Well-known Member
... 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

##### New Member
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

##### MrExcel MVP, Moderator
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 )

Replies
4
Views
175
Replies
7
Views
120
Replies
10
Views
278
Replies
12
Views
360
Replies
6
Views
153

1,127,120
Messages
5,622,855
Members
415,934
Latest member

### 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.

### Which adblocker are you using?

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

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