Lookup "TEXT" in certain "CELLS" and return the value 2 rows above the "TEXT" into another "SHEET" "CELL", Excel 2021

[HKA_84]

Hello,

I'm trying to make a formula to solve my problem, if it is possible.
I tried searching many formula online but it didn't fit in the formula I want to use on my file

on SHEET 2 CELL (B12) write formula, IF(B4:H4) and (B7:H7), contains the TEXT "Billy 1" then return the value 2 rows above the answer "Billy 1" that is the Date (Shown the Date 22-Jan-22) into Sheet 1 CELL (B4).

another Cell but same question

on SHEET 2 CELL (B14) write formula, IF(B4:H4) and (B7:H7), contains the TEXT "Billy 2" then return the value 2 rows above the answer "Billy 2" that is the Date (Shown the Date 25-Jan-22) into Sheet 1 CELL (C4).

another Cell but same question different name

on SHEET 2 CELL (F12) write formula, IF(B4:H4) and (B7:H7), contains the TEXT CAPS LOCK "MARY 5" then return the value 5 rows above the answer "MARY 5" that is the Date (Shown the Date 21-Jan-22) into Sheet 1 CELL (F8).

SHEET 2

Example.xlsx
	A	B	C	D	E	F	G	H
1
2		21-Jan-22	22-Jan-22	23-Jan-22	24-Jan-22	25-Jan-22	26-Jan-22	27-Jan-22
3
4			Billy 1			Billy 2
5
6
7		MARY 5			Billy 3
8
9
10
11
12
13
14
15
Sheet2

SHEET 1

Example.xlsx
	A	B	C	D	E	F	G	H
2
3		Billy 1	Billy 2	Billy 3	Billy 4	Billy 5	Billy 6
4
5
6
7		MARY 1	MARY 2	MARY 3	MARY 4	MARY 5	MARY 6
8
9
Sheet1

I hope it can be solved using formula but if cannot. I can try to use VBA.
Thanks.

 

[Peter_SSs]

I don't understand what B12, B14 and F12 on Sheet2 have to do with it. As best I could understand what you are wanting is this.

HKA_84.xlsm
	A	B	C	D	E	F	G
1
2
3		Billy 1	Billy 2	Billy 3	Billy 4	Billy 5	Billy 6
4		22-Jan-22	25-Jan-22	24-Jan-22
5
6
7		MARY 1	MARY 2	MARY 3	MARY 4	MARY 5	MARY 6
8						21-Jan-22
Sheet1

Cell Formulas
Range	Formula
B4:G4,B8:G8	B4	=IF(COUNTIF(Sheet2!$B$3:$H$7,B3),MIN(IF(Sheet2!$B$3:$H$7=B3,Sheet2!$B$2:$H$2,"")),"")

 

[HKA_84]

Hi Peter,

Thank you very much for your reply.
This solve one of my solution

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[HKA_84]

I must thank you a lot, it really helped me..
I have another post to solve please, if you can help me also with it
here is the link -->> Return Date above the Certain "TEXT" from the "NAME" in (Excel2021)

 

[HKA_84]

It is slightly a bit same format as this one but just Added Name in it and the Billy and Mary is replaced by PH 1 and so on
what I want to do in it is

Search for PH 1 (2022) but follow by the Name, so Example Billy or Mary can have PH 1 (2022) or PH 2 (2022) or PH 5 (2021) and return its Date value 2row above into Sheet "PUBLIC HOLIDAY", According to the Name, PH 1 and year

Thank you for the great help again..