Search&if function issue

[Greenbug]

Hello Community:
Im Currently have an issue with the Search&If function. I will try to discribe it and hope someone could help me out. Its a littel bit complex.

Lets say I have two different sheets name "Sheet1" and "Sheet2".

When I typ a certain text(Hi/Hello/Greetings) in a cell in "Sheet2". Another Text will be filled in "Sheet1" based on some search conditions in "Sheet2". Text other than Hi/Hello/Greetings will be ignored.

See examples below:
1.PNG
we focus on row 12. if C3 is Hi, and C1 is C, and C2 is empty, then in sheet2,
2.PNG

Peter should ramdonly appeard in one of the four rows in red field 1 because C1 is C, C2 is empty, C3 is Hi.

If both C1 and C2 are not empty, C1 would be ignored. So Jaina should ramdonly appeard in one of the four rows in red field 2 in field B, because C3 is Hello, even though C1 is A but C2 is B, C1 would be ignored so Jaina wouldnt be appeard in field A but in field B.

the thing is that,i dont have to typ name in each of the red field in sheet 2, I only need to typ Hi/Hello/Greetings in sheet1, and the name will automaticlly appeard in sheet2 base on some search conditions.

I hope you guys can understand what Im try to say.
Thank you!

Grüße aus Deutschland!
Greenbug

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post