Potential Index Formula Question...

[V L]

Friends: I am in need of the following assistance. I have dates in cells F17 to F36. Some cells contain blanks. I have dollar values in Q17 to Q36. Some cells may also contain “---“. I need a formula to do the following. In cell C8, I would like to show the dollar value from range Q17 to Q36 which corresponds to the last cell containing a date in the range F17 to F36. I believe this involves the index formula but am unsure. Thank you for the assistance in advance.

 

[hagia_sofia]

With 365 you could try:

=TAKE(FILTER(Q17:Q36,(F17:F36<>"")*(Q17:Q36<>"---")),-1)

 

[felixstraube]

Would this work?:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7
8			36.00
9
10
11
12
13
14
15
16
17						25/07/2024											30
18						26/07/2024											36
19						27/07/2024											43
20						28/07/2024											45
21						29/07/2024											49
22						30/07/2024											44
23						31/07/2024											44
24																	---
25						02/08/2024											29
26						03/08/2024											50
27																	---
28						05/08/2024											13
29						06/08/2024											26
30						07/08/2024											45
31						08/08/2024											31
32						09/08/2024											30
33						10/08/2024											36
34
35
36
Sheet1

Cell Formulas
Range	Formula
C8	C8	=INDEX(Q17:Q36,MAX((F17:F36<>"")*(Q17:Q36<>"---")*ROW(F17:F36))-16)

 

[AhoyNC]

Another option:

Book3
	C	D	E	F	G									P	Q
8	36
9
17				07/25/24											30
18				07/26/24											36
19				07/27/24											43
20				07/28/24											45
21				07/29/24											49
22				07/30/24											44
23				07/31/24											44
24															---
25				08/02/24											29
26				08/03/24											50
27															---
28				08/05/24											13
29				08/06/24											26
30				08/07/24											45
31				08/08/24											31
32				08/09/24											30
33				08/10/24											36
34
35
36
Sheet1

Cell Formulas
Range	Formula
C8	C8	=LOOKUP(9.999999999999E+307,F17:F36,Q17:Q36)

 

[V L]

With 365 you could try:

=TAKE(FILTER(Q17:Q36,(F17:F36<>"")*(Q17:Q36<>"---")),-1)

This worked very well. TY so much....