look up year in non contiguous columns and return date if current year

[CONORF]

Good morning. Hoping someone can help. I have data with dates in non contiguous columns and I want to look at all the columns and return the date if the year is 2023. I haven't been able to do a formula that looks at the data in the different columns and if the year is 2023 to return the date any help would be appreciated. Thank you.

 

[Skybluekid]

Hi

Try

Book1
	A	B	C	D	E	F
1	Current Yr EffDate by beg of Qtr	Current Yr Eff Date by beg of Qtr	'LCurrent Yr. Eff Date by	Current Yr Eff Date by beg of Qtr
2
3
4
5
6				04/08/2023		04/08/2023
7
8
9
10
11
12
13
14
15
16	07/11/2023					07/11/2023
17
18				04/08/2023		04/08/2023
19	28/01/2023					28/01/2023
20	28/01/2023					28/01/2023
21
22
23
24	04/08/2023					04/08/2023
25		07/01/2023				07/01/2023
Sheet7

Cell Formulas
Range	Formula
F6:F25	F6	=IFERROR(INDEX(A6:D6,MATCH(YEAR(TODAY()),YEAR(A6:D6),0)),"")

 

[CONORF]

Thank you! that would work great if the columns were contiguous. But they aren't. Any thoughts on how to modify the formula to check non contiguous columns? Appreciate the quick response.

 

[Skybluekid]

It would not matter if they were not as the formula will only be looking for the Year in that row

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Current Yr EffDate by beg of Qtr					Current Yr Eff Date by beg of Qtr					'LCurrent Yr. Eff Date by						Current Yr Eff Date by beg of Qtr
2		a	b	c	d		a	b	c	d		a	b	c	d	e
3		a	b	c	d		a	b	c	d		a	b	c	d	e
4		a	b	c	d		a	b	c	d		a	b	c	d	e
5		a	b	c	d		a	b	c	d		a	b	c	d	e
6		a	b	c	d		a	b	c	d		a	b	c	d	e	04/08/2023		04/08/2023
7		a	b	c	d		a	b	c	d		a	b	c	d	e
8		a	b	c	d		a	b	c	d		a	b	c	d	e
9		a	b	c	d		a	b	c	d		a	b	c	d	e
10		a	b	c	d		a	b	c	d		a	b	c	d	e
11		a	b	c	d		a	b	c	d		a	b	c	d	e
12		a	b	c	d		a	b	c	d		a	b	c	d	e
13		a	b	c	d		a	b	c	d		a	b	c	d	e
14		a	b	c	d		a	b	c	d		a	b	c	d	e
15		a	b	c	d		a	b	c	d		a	b	c	d	e
16	07/11/2023	a	b	c	d		a	b	c	d		a	b	c	d	e			07/11/2023
17		a	b	c	d		a	b	c	d		a	b	c	d	e
18		a	b	c	d		a	b	c	d		a	b	c	d	e	04/08/2023		04/08/2023
19	28/01/2023	a	b	c	d		a	b	c	d		a	b	c	d	e			28/01/2023
20	28/01/2023	a	b	c	d		a	b	c	d		a	b	c	d	e			28/01/2023
21		a	b	c	d		a	b	c	d		a	b	c	d	e
22		a	b	c	d		a	b	c	d	14/01/2023	a	b	c	d	e			14/01/2023
23		a	b	c	d		a	b	c	d		a	b	c	d	e
24	04/08/2023	a	b	c	d		a	b	c	d		a	b	c	d	e			04/08/2023
25		a	b	c	d	07/01/2023	a	b	c	d		a	b	c	d	e			07/01/2023
Sheet7

Cell Formulas
Range	Formula
S6:S25	S6	=IFERROR(INDEX(A6:Q6,MATCH(YEAR(TODAY()),YEAR(A6:Q6),0)),"")

 

[CONORF]

Makes sense. I apologize. I should have been more clear. The values in the current yr eff date column have the current year but so do the hidden columns so this is returning the eff date when what I need is the eff date by beg of qtr column. I once again appreciate your help. Below is the screen shot showing that it is returning the 5/1/23 date when what i need is the 4/8/23 date.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)