Can you reference a cell, then return a value from a different cell, based on the first cell's specific text?

[Dais Helper]

Hello,
I'm managing our graduation roster. I have columns A through AQ in use for various data; and currently using rows up to 253.
Column A: Invite Status (this is where I input yes, no, tentative, etc. via a dropdown)
Column Q: Major (lists their major )
Column AQ: Attending Majors (this is where I want results to go)

If I have one of these answers in Column A for a graduate the in a row __:
YES - Survey
YES - Phone or Email
YES - In Person
Tentative YES - Survey

I want column AQ to show the appropriate Major (taken from Column Q) associated with the graduate who's planning to attend the graduation ceremony.

Is it possible that there is a formula that can be used in Column AQ to get this result in AQ? Or use any columns at/beyond AR to assist...

I don't understand formulas or VBA, but I can usually follow directions if written for the novice.

Unfortunately our IT department seems to have blocked the ability for the xl2bb add-in. I sort of recreated it below on how I'd like it to look...

A​	...	Q​	...	AQ​
Invite Status	hidden columns	Major	hidden columns	Attending Majors
(any of the Yes responses above)	---	Bachelor of Science in Dietetics & Nutrition	--	Bachelor of Science in Dietetics & Nutrition
(any other text or blank entries)	---	Masters in Psychology	--

Thank you in advance.

 

[ExceLoki]

try this

Book1
	A																Q																										AQ
1	invite status																Major																										Attending Majors
2	yes																Marjor 1																										Marjor 1
3	no																Marjor 1
4	yes - survey																Marjor 2																										Marjor 2
5																	Marjor 2
Sheet1

Cell Formulas
Range	Formula
AQ2:AQ5	AQ2	=IF(COUNTIF(A2,"*yes*"),Q2,"")

 

[Candyman8019]

Try something like this in AQ:

=IF(OR(A2="Survey",A2="Phone or Email",A2="In Person", A2="Tentative"),Q2,"")

 

[Dais Helper]

@ExceLoki -- Sweet! I was able to get this formula to work beautifully. Thank you so much.

@Candyman8019 -- unfortunately I was unable to get the formula to work.

 

[ExceLoki]

happy to help, thanks for the feedback