VLOOKUP based on 2 headers of a single column (perhaps Index/Match is better)

[Bjomesphat]

I'm trying to do a vlookup (or an index/match if those functions are better suited), to find values based on the matching date, as well as 2 headers on a single column, and then I need to sum the returned values.

The challenge I'm running into, is that I need to pull the data that matches the same date, but it can only be from columns with PHX and I need to sum the value from these two SKUs: 12383 and 12386. Keep in mind, columns with BVL have these same SKU numbers, hence why I need to be able to differentiate between BVL and PHX columns.

I was able to do it one way with vlookup, but it depends on my columns always being in the exact same location, but I can't always guarantee that. This is that formula:
=IFNA(SUMPRODUCT(VLOOKUP(B$5,Actual!$A$3:$J$7,{7,10},FALSE)),0)

Here is the table I'm trying to fill in values for:

test file.xlsx
	A	B	C	D	E	F	G	H
4	PHX	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5		11/13/2022	11/14/2022	11/15/2022	11/16/2022	11/17/2022	11/18/2022	11/19/2022
6	Forecast	477,036	240,729	226,519	243,000	217,078	206,004	235,310
7	Actual	396,895
Sheet1

Cell Formulas
Range	Formula
B7	B7	=IFNA(SUMPRODUCT(VLOOKUP(B$5,Actual!$A$3:$J$7,{7,10},FALSE)),0)

And here is the data I'm pulling from on a separate sheet:

test file.xlsx
	A	B	C	D	E	F	G	H	I	J
1		BVL	BVL	BVL	BVL	PHX	PHX	PHX	PHX	PHX
2	Date/SKU	12383	12384	12385	12386	5103	12383	12384	12385	12386
3	11/13/2022	1186860	52703	16384	241571	39	332025	16661	3946	64870
4	11/14/2022	784999	37496	10766	152538	21	267203	13572	3735	48008
5	11/15/2022	691496	42979	10670	146355	22	207591	2639	1715	37025
6	11/16/2022	692021	36898	10420	126486	13	215353	9301	2901	39365
7	11/17/2022	571972	27339	8291	112066	22	196477	9585	2890	44080
Actual

Appreciate any and all help.

 

[Peter_SSs]

See if this does what you want.

Bjomesphat.xlsm
	A	B	C	D	E	F	G	H
4	PHX	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5		13/11/2022	14/11/2022	15/11/2022	16/11/2022	17/11/2022	18/11/2022	19/11/2022
6	Forecast
7	Actual	396,895	315,211	244,616	254,718	240,557	0	0
Sheet1

Cell Formulas
Range	Formula
B7:H7	B7	=SUM(FILTER(FILTER(Actual!$B3:$J7,(Actual!$B1:$J1=$A4)*((Actual!$B2:$J2=12383)+(Actual!$B2:$J2=12386)),0),Actual!$A3:$A7=B5,0))

 

[Bjomesphat]

@Peter_SSs

Works perfectly. Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.