Index Match Formula to retrieved next cell value

[RSI1989]

Hi All, I need your help.
I have a table which shows the schedule of one of the staff - table shown below.

01-Jan​

02-Jan​

03-Jan​

04-Jan​

05-Jan​

06-Jan​

07-Jan​

08-Jan​

09-Jan​

10-Jan​

11-Jan​

12-Jan​

13-Jan​

14-Jan​

15-Jan​

16-Jan​

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Mark

Sick

OFF

Absent

05_14

05_14

Absent

Absent

OFF

OFF

05_14

05_14

05_14

05_14

05_14

OFF

OFF

What will be the correct formula to show the dates of Mark's ABSENT. The output I looking for should be as below

3-Jan-23​

6-Jan-23​

7-Jan-23​

 

[JamesCanale]

I think this will work in 2016:

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2		1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	16-Jan
3		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
4	Mark	Sick	OFF	Absent	05_14	05_14	Absent	Absent	OFF	OFF	05_14	05_14	05_14	05_14	05_14	OFF	OFF
5
6		1/3/2023	1/6/2023	1/7/2023
Sheet3

Cell Formulas
Range	Formula
B6:D6	B6	=SMALL(IF(B4:Q4="Absent",B2:Q2,99999),TRANSPOSE(ROW(INDIRECT("1:"&3))))
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		01/01/2023	02/01/2023	03/01/2023	04/01/2023	05/01/2023	06/01/2023	07/01/2023	08/01/2023	09/01/2023	10/01/2023	11/01/2023	12/01/2023	13/01/2023	14/01/2023	15/01/2023	16/01/2023
2		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
3	Mark	Sick	OFF	Absent	05_14	05_14	Absent	Absent	OFF	OFF	05_14	05_14	05_14	05_14	05_14	OFF	OFF
4
5		03/01/2023	06/01/2023	07/01/2023
Sheet7

Cell Formulas
Range	Formula
B5:E5	B5	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$Q$1)/($B$3:$Q$3="absent"),COLUMNS($B1:B1))),"")

 

[RSI1989]

Thank you so much for your time and effort. Perfect formula and working properly

 

[Fluff]

Not sure which of us you are talking to, but glad we could help.