Conditional Formula, based on a value, that then looks for cells part containing a defined value

[FelixScatt]

Hi

I am trying to write a formula that does the following:

In Sheet1 cell C3 I have a List of values (Customer1, Customer2, etc)

The value in Sheet1 cell C4 is dependent on the value in Sheet1 cell C3.

Sheet1 cell C3 contains the Customer Name

Sheet1 cell C4 contains a Data Validation List of Branches for that Customer.

When a Customer is selected in Sheet1 cell C3, Sheet1 cell C4 is blank by default. The user then selects a Branch from the subsequent list.

I am trying to write an excel statement that checks Sheet1 cell C4 to see if it is blank.

If it is blank then I want it to look down a list of values in Sheet2 column B to see if one of the valid values contains the words "Head Office" (or "Head" or "Office").  If it finds a valid entry in this selection then I want Sheet 1 cell D3 to populate with the Head Office Address (which is referenced in Sheet2 columns C-G.

Can anyone help with this formula please?

I believe "IsNumber", "Search" and "Filter" should do the job, but I am lost on order, and the structure the formula would need to take. This is where I am up to, but these don't work.

=IF(C4="",(ISNUMBER(SEARCH("Head Office",(FILTER(Branch!$B$3:$B$63,(Branch!$B$3:$B$63=C4)," "))))))

=FILTER(Branch!$B$3:$B$64,(Branch!$A$3:$A$64=C3)*((Branch!$B$3:$B$64=C4)+(Branch!$B$3:$B$64="Head Office")))

As a simple test, I know that this works: 

=IF(ISNUMBER(SEARCH("Head",B70,1)),"Found","Not Found")

[#2]

@FelixScatt Best to upload an example file, as it becomes a bit abstract imagining how your real schedule looks like. Make sure you remove any private and confidential information.

[#3]

Thanks for the advice @Riny_van_Eekelen.  I'll try again, with context...

Cell C3 contains a Data Validation list.

Cell C4 contains a Data Validation list which is dependent on the value in C3.

As the image provided shows, C4 has 3 Branch options: Cust1 Head Office, Cust1 S1, and Cust1 S2.

If Cell C4 contains a value, then Cells H3:H6 are populated based on the conditional value.

Aim:

What I am trying to achieve is for H3:H6 to default to the value that contains "Head Office" if Cell C4 is blank.  It needs to lookup the range of values to find the cell that contains "Head Office" as part of its value, which will then return the values into the H Cells.

[#4]

@FelixScatt Better to upload the file with some example data in it. Not just a screenshot.

[#5]

@Riny_van_Eekelen.   Working example attached.

[#6]

@FelixScatt See if the attached is acceptable for you. Column L:M on the main sheet, that is.

[#7]

@Riny_van_Eekelen That looks spot on!  Thank you so much.  Much appreciated.