How to have the statement check the next row if the IF AND statement is false

[mrsspdy]

I am pulling data from one worksheet to another if the value of cell A9 (on the second worksheet) is not blank AND the value of a cell in D9 (on the second worksheet) equals a specific word or text string return the value of A9. If the criteria is not met I need it to check the next row and return the value of B9, etc. I have tried using the IF AND function but cannot figure out how to make it check the next row if the criteria is not met. I saw an answer to a similar question from 2018 that was answered using a formula containing the INDEX function, but I just don't understand formulas well enough to apply it to my data.

Here is the formula I am typing --=IF(AND('Verifications - PMO'!A9<>"", 'Verifications - PMO'!D9="Area 4"), 'Verifications - PMO'!A9,"")

Original spreadsheet:

What I am getting back:

Expected return:

This may be a short-lived project since columns D on have nested IF functions that relate back to the A Column of the original cell i.e, =IF($A7="","",IF('Verifications - PMO'!E9="PW","Paperwork",IF('Verifications - PMO'!E9="N/T",1,IF('Verifications - PMO'!E9="T",2,IF(AND(ISNUMBER('Verifications - PMO'!E9),'Verifications - PMO'!E9>0,NOT(ISERROR(SEARCH("(QT)",$A7)))),4,IF(AND(ISNUMBER('Verifications - PMO'!E9),'Verifications - PMO'!E9>0),3,IF('Verifications - PMO'!E9="","Check",IF('Verifications - PMO'!E9="N/R","Not Required"))))))))

If I am reading this right it means it that if A7 has data (the new operator name) then it will be pull the data from the column E9 of the original spreadsheet which relates to the old operator name. Is that correct?

Thanks in advance for your guidance.

 

[kevin9999]

I note your profile shows you have 365, therefore the Filter() function is available to you. Here's one possible solution:
From this data source:

Book1
	A	B	C	D
1	Employee	USER ID	Shift	Area
2	Maximoff, Wanda		C	Area 2
3	Parker, Peter		C	Area 2
4	Richards, Susan		B	Area 3
5	Stark, Tony		D	Area 5
6	Van Dyne, Janet		C	Area 4
7	Walker, Patsy		B	Area 4
8	Wayne, Bruce		D	Area 2
9	Whitman, Dane		D	Area 2
Verifications - PMO

You could do this:

Book1
	A	B	C
1	Employee	Shift	Area
2	Van Dyne, Janet	C	Area 4
3	Walker, Patsy	B	Area 4
4
Sheet2

Cell Formulas
Range	Formula
A2:C3	A2	=FILTER(FILTER('Verifications - PMO'!A2:D9,('Verifications - PMO'!D2:D9<>"")*('Verifications - PMO'!D2:D9="Area 4")),{1,0,1,1})
Dynamic array formulas.

 

[kevin9999]

Typo in the previous post, should have been (well spotted @Alex Blakenburg )

Book1
	A	B	C
1	Employee	Shift	Area
2	Van Dyne, Janet	C	Area 4
3	Walker, Patsy	B	Area 4
4
Sheet2

Cell Formulas
Range	Formula
A2:C3	A2	=FILTER(FILTER('Verifications - PMO'!A2:D9,('Verifications - PMO'!A2:A9<>"")*('Verifications - PMO'!D2:D9="Area 4")),{1,0,1,1})
Dynamic array formulas.