Help w/ a simple Index/Match in Pivot Table

[ExcelAtEverything]

Ok, this may be a lot of text, but it shouldn't be THAT complicated of a problem. I just want to be sure I'm thorough in my explanation.

Below is a mocked up version of something I'm having a problem with. Imagine that the below range is a Power Pivot Table, which has additional sales numbers added to it as each day passes (via Power Query). The dates in row 3 will always ne Jan-December of the current year, and the categories in column B will pretty much always be the same. Also note that each date has 3 sections (delivery, in-store, and pickup). I've collapsed a bunch of columns just to show that these are not short rows of info. There are 3 columns for each day of the year.

Additionally, there is a Date cell in different sheet in this workbook which shows the date that the reporting should be looking at (the date is manually input by the user into that cell). For the sake of this formula that I'm asking for help with, let's refer to that date as Details!G$3 and assume that the date in it right now is 5/31/2021.

So what I need is a formula to place into my "In-Store Totals" column (not seen below) which will check the date (located at Details!$G$3, showing 5/31/2021), and as a result will return the word "Fish" because the number "8883" is the highest number in the 5/31/2021 "In-Store" column. The formula needs to be done in such a way that it allows for the category to change from day to day. In other words if I decide to look at 5/31's "Delivery" column, then the correct return would be "Bread", because the largest number is 1023.

Unless I'm mistaken this will work better as an Index/Match formula. I just can't get it to work correctly. As part of my Index/Match, I was using LARGE(MaxCat!BH5:BH13,1) to get the 8883, but I kept erroring out when I tried to use in conjunction with the MATCH function. Here is the gist of what I'm looking for, but this of course does not work.
=INDEX($B$3:$FN$13,
MATCH(LARGE(MaxCat!BH5:BH13,1),BH5:BH13,0),
MATCH(Details!G$3,$B$3:$FN$13,0))

Thanks for any help.

 

[Alex Blakenburg]

Since your user is going to have to enter a date and status (Delivery,In-Store,Pickup), why not create a pivot table that just does that.

Below is a formula that might work for you but you would have to change the data ranges.
Apart from the column B reference for category each of the other data ranges appear twice.

20210605 Index Match 2 headings v02.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3			24/05/2021			31/05/2021					Input	Result
4			Delivery	In-Store	Pickup	Delivery	In-Store	Pickup		Your G3 -->	31/05/2021	Sugar
5		Eggs	10.00	100.00	1,000.00	10,000.00	100,000.00	1,000,000.00			Pickup
6		Milk	20.00	200.00	2,000.00	20,000.00	500,000.00	2,000,000.00
7		Fish	30.00	300.00	5,000.00	50,000.00	300,000.00	3,000,000.00
8		Sugar	40.00	400.00	4,000.00	40,000.00	400,000.00	4,000,000.00			$B$5:$B$8	Category
9											$C$5:$H$8	Data Body Range
10											$C$3:$H$3	Heading Date Line
11											$C$4:$E$4	Heading Status - 1st 3 columns - match to determine if we want to offset 1, 2 or 3 (converts to 0,1,2)
12
Sheet1

Cell Formulas
Range	Formula
L4	L4	=INDEX($B$5:$B$8, MATCH(LARGE(INDEX($C$5:$H$8,0,MATCH($K$4,$C$3:$H$3,0)+MATCH($K$5,$C$4:$E$4,0)-1),1), INDEX($C$5:$H$8,0,MATCH($K$4,$C$3:$H$3,0)+MATCH($K$5,$C$4:$E$4,0)-1), 0), 0)

 

[ExcelAtEverything]

Thanks Alex! Let me sit with this for a bit and think about it.