XLOOKUP with multiple criteria across multiple columns and rows

[soriaul]

Hello,
I'm trying to see if there is an xlookup formula or any formula that will help, that will bring back a total that has criteria across columns and a row. This is what I tried previously and it's not working and bringing back a value error.

XLOOKUP(B9,Assumptions!$B$3:$B$152,XLOOKUP(D$6,Assumptions!$C$2:$N$2,XLOOKUP(Cashflow!$D$2,Assumptions!$A$3:$A$152,Assumptions!C$3:C$152,0)))

Below in the "Sales" row is where I want the values returned based on the criteria in red font

1

Financial Statements

$ Thousands

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

2024​

April

May

June

July

August

September

October

November

December

January

February

March

INCOME STATEMENT

Sales

Sales Proceeds

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

This is the table where the data is coming from.

Project #	Type	April	May	June	July	August	September	October	November	December	January	February	March
1​	Sales	-	-	-	-	-	-	-	-	2.11	-	-	-
2​	Sales	-	-	-	-	-	-	-	-	-	-	-	1.48
3​	Sales	-	-	1.35	-	-	-	-	-	-	-	-	-
4​	Sales	-	-	-	-	-	2.36	-	-	-	-	-	-
5​	Sales	-	-	-	-	-	-	-	-	1.80	-	-	-
6​	Sales	-	-	-	-	-	-	-	-	-	-	1.90	-
7​	Sales	-	-	-	-	-	-	-	-	3.14	-	-	-
8​	Sales	-	-	3.36	-	-	-	-	-	-	-	-	-
9​	Sales	-	-	-	-	-	-	-	2.76	-	-	-	-
10​	Sales	-	-	-	-	-	-	2.98	-	-	-	-	-
11​	Sales	-	-	-	-	-	-	-	-	1.96	-	-	-
12​	Sales	-	-	-	-	-	1.93	-	-	-	-	-	-
13​	Sales	-	-	-	-	2.02	-	-	-	-	-	-	-
14​	Sales	-	-	2.84	-	-	-	-	-	-	-	-	-
15​	Sales	-	-	-	-	-	-	-	-	-	3.77	-	-
16​	Sales	-	-	-	-	-	-	3.59	-	-	-	-	-
17​	Sales	-	-	-	-	-	-	-	-	-	-	-	2.85
18​	Sales	-	-	2.62	-	-	-	-	-	-	-	-	-
19​	Sales	-	-	-	-	-	1.54	-	-	-	-	-	-
20​	Sales	-	-	-	-	-	-	-	4.27	-	-	-	-
21​	Sales	-	-	-	3.37	-	-	-	-	-	-	-	-
22​	Sales	-	-	-	-	-	3.33	-	-	-	-	-	-
23​	Sales	-	-	-	-	-	-	-	-	-	-	-	2.25
24​	Sales	-	-	-	-	-	-	-	3.08	-	-	-	-
25​	Sales	-	-	1.69	-	-	-	-	-	-	-	-	-
26​	Sales	-	-	-	-	-	1.06	-	-	-	-	-	-
27​	Sales	-	-	-	-	-	2.83	-	-	-	-	-	-
28​	Sales	-	-	-	-	-	-	2.09	-	-	-	-	-

 

[AhoyNC]

Like this?
Drag formula in C7 across columns as needed.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Financial Statements
2		$ Thousands	1	2	3	4	5	6	7	8	9	10	11	12
3			2024
4			April	May	June	July	August	September	October	November	December	January	February	March
5
6		INCOME STATEMENT
7	Sales	Sales Proceeds	0	0	11.86	3.37	2.02	13.05	8.66	10.11	9.01	3.77	1.9	6.58
8
9	This is the table where the data is coming from.
10	Project #	Type	April	May	June	July	August	September	October	November	December	January	February	March
11	1	Sales	-	-	-	-	-	-	-	-	2.11	-	-	-
12	2	Sales	-	-	-	-	-	-	-	-	-	-	-	1.48
13	3	Sales	-	-	1.35	-	-	-	-	-	-	-	-	-
14	4	Sales	-	-	-	-	-	2.36	-	-	-	-	-	-
15	5	Sales	-	-	-	-	-	-	-	-	1.8	-	-	-
16	6	Sales	-	-	-	-	-	-	-	-	-	-	1.9	-
17	7	Sales	-	-	-	-	-	-	-	-	3.14	-	-	-
18	8	Sales	-	-	3.36	-	-	-	-	-	-	-	-	-
19	9	Sales	-	-	-	-	-	-	-	2.76	-	-	-	-
20	10	Sales	-	-	-	-	-	-	2.98	-	-	-	-	-
21	11	Sales	-	-	-	-	-	-	-	-	1.96	-	-	-
22	12	Sales	-	-	-	-	-	1.93	-	-	-	-	-	-
23	13	Sales	-	-	-	-	2.02	-	-	-	-	-	-	-
24	14	Sales	-	-	2.84	-	-	-	-	-	-	-	-	-
25	15	Sales	-	-	-	-	-	-	-	-	-	3.77	-	-
26	16	Sales	-	-	-	-	-	-	3.59	-	-	-	-	-
27	17	Sales	-	-	-	-	-	-	-	-	-	-	-	2.85
28	18	Sales	-	-	2.62	-	-	-	-	-	-	-	-	-
29	19	Sales	-	-	-	-	-	1.54	-	-	-	-	-	-
30	20	Sales	-	-	-	-	-	-	-	4.27	-	-	-	-
31	21	Sales	-	-	-	3.37	-	-	-	-	-	-	-	-
32	22	Sales	-	-	-	-	-	3.33	-	-	-	-	-	-
33	23	Sales	-	-	-	-	-	-	-	-	-	-	-	2.25
34	24	Sales	-	-	-	-	-	-	-	3.08	-	-	-	-
35	25	Sales	-	-	1.69	-	-	-	-	-	-	-	-	-
36	26	Sales	-	-	-	-	-	1.06	-	-	-	-	-	-
37	27	Sales	-	-	-	-	-	2.83	-	-	-	-	-	-
38	28	Sales	-	-	-	-	-	-	2.09	-	-	-	-	-
Sheet1

Cell Formulas
Range	Formula
C7:N7	C7	=SUM(FILTER($C$11:$N$38,C4=$C$10:$N$10,0))

 

[MountainFog]

Unfortunately, your question is hard to piece together because there aren't any column/row headers, and judging from the dataset, you don't start in A1.

Because of this, I am just using the below named/ dynamic ranges (which would be good to do anyway):

Table is converted into a dynamic table named ProjectTable
Red 1 in your first screenshot is named ProjectNum
Red Sales in your first screenshot is named Type
Red Month is named Month

=INDEX(ProjectTable,XMATCH(1,(ProjectTable[Project '#]=ProjectNum)*(ProjectTable[Type]=Type)),XMATCH(Month,ProjectTable[#Headers]))

 

[soriaul]

Thanks this is closer but I also need to add in the filter by project #

 

[MountainFog]

This wouldn't filter based on Project Number or Type, although could be modified to do it.

 

[MountainFog]

Thanks this is closer but I also need to add in the filter by project #

My result does filter by project number

 

[soriaul]

My result does filter by project number

That worked! Thanks so much!