Return value based on two criteria

[Sean15]

Hello:

In C19, I want to lookup date in B2, return it's value in D2 using criteria in C18 and B19
Expected value is shown in C19. Could you offer a suggestion please?

PW 070222.xlsx
	A	B	C	D
2	1-Nov-22	RUBS - Water and Sewer - Water For 09/01/22-09/30/22	Property	40.73
3	1-Nov-22	RUBS - Trash - Trash For 09/01/22-09/30/22	Property	6.54
4	1-Nov-22	Tax - Tax For 09/01/22-09/30/22	Property	3.73
5	1-Nov-22	RUBS - Water and Sewer - Sewer For 09/01/22-09/30/22	Property	6.80
6	1-Nov-22	RUBS Fee - Service Fee For 11/01/22-11/30/22	Property	4.25
7	1-Nov-22	RUBS - Gas - Gas For 09/01/22-09/30/22	Property	8.05
8	1-Nov-22	RUBS - Electric - Electric For 09/01/22-09/30/22	Property	71.73
9	1-Nov-22	Monthly RUBS - Pest Control - Posted from 11/01/2022 to 11/30/2022 - RUBS - Pest Control	Property	5.12
10	1-Nov-22	Monthly Valet Trash - Posted from 11/01/2022 to 11/30/2022 - Valet Trash	Property	25.58
11	1-Nov-22	Rent	Property	1,084.38
12	1-Nov-22	Monthly Facility Fee - Posted from 11/01/2022 to 11/30/2022 - Facility Fee	Property	12.22
13	1-Nov-22	Monthly Parcel - Posted from 11/01/2022 to 11/30/2022 - Parcel	Property	2.05
14	1-Nov-22	Credit - Valet Trash - Valet Trash Adjustment 11/2022	Property	(25.58)
15
16
17			Expected result by date
18			11/1/2022
19		water	40.73
20		trash	6.54
21		tax	3.73
22		sewer	6.80
23		service fee	4.25
24		gas	8.05
25		electric	71.73
26		pest control	5.12
27		valet trash	-
28		rent	1,084.38
29		facility fee	12.22
30		parcel	2.05
31			1,245.60
32
Sheet1

Cell Formulas
Range	Formula
C31	C31	=SUM(C19:C30)

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C	D
1
2	01/11/2022	RUBS - Water and Sewer - Water For 09/01/22-09/30/22	Property	40.73
3	01/11/2022	RUBS - Trash - Trash For 09/01/22-09/30/22	Property	6.54
4	01/11/2022	Tax - Tax For 09/01/22-09/30/22	Property	3.73
5	01/11/2022	RUBS - Water and Sewer - Sewer For 09/01/22-09/30/22	Property	6.8
6	01/11/2022	RUBS Fee - Service Fee For 11/01/22-11/30/22	Property	4.25
7	01/11/2022	RUBS - Gas - Gas For 09/01/22-09/30/22	Property	8.05
8	01/11/2022	RUBS - Electric - Electric For 09/01/22-09/30/22	Property	71.73
9	01/11/2022	Monthly RUBS - Pest Control - Posted from 11/01/2022 to 11/30/2022 - RUBS - Pest Control	Property	5.12
10	01/11/2022	Monthly Valet Trash - Posted from 11/01/2022 to 11/30/2022 - Valet Trash	Property	25.58
11	01/11/2022	Rent	Property	1084.38
12	01/11/2022	Monthly Facility Fee - Posted from 11/01/2022 to 11/30/2022 - Facility Fee	Property	12.22
13	01/11/2022	Monthly Parcel - Posted from 11/01/2022 to 11/30/2022 - Parcel	Property	2.05
14	01/11/2022	Credit - Valet Trash - Valet Trash Adjustment 11/2022	Property	-25.58
15
16
17			Expected result by date
18		you must put the same format as the dates in column A --->	01/11/2022
19		water	40.73
20		trash	6.54
21		tax	3.73
22		sewer	6.8
23		service fee	4.25
24		gas	8.05
25		electric	71.73
26		pest control	5.12
27		valet trash	0
28		rent	1084.38
29		facility fee	12.22
30		parcel	2.05
31			1245.6
Sheet1

Cell Formulas
Range	Formula
C19:C30	C19	=SUM(($A$2:$A$14=$C$18)*( (ISNUMBER(SEARCH(B19&" for ",$B$2:$B$14))) + (ISNUMBER(SEARCH(B19&" - posted from ",$B$2:$B$14))) + (ISNUMBER(SEARCH(B19&" adjustment ",$B$2:$B$14))) + (IF(B19=$B$2:$B$14,1,0))) * ($D$2:$D$14))
C31	C31	=SUM(C19:C30)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sean15]

@DanteAmor, very clever, thank you so much. I am not familiar with Array formulas.
Could you post a formula that does not require CTRL+SHIFT+ENTER?

With Thanks,

 

[DanteAmor]

I see that you have excel 2019, hopefully someone else will support us with a formula for that version and that does not require CSE