Search Results over more than one row

[Bilabu1]

Hi,

I have following problem:

In the attached workbook I have a sheet where I need the answer "yes" or "no" in cell J based on the information in cell I. "Yes" if one of the cells in I does not have Frankfurt in it. The question is if a firm contact is known in another office than Frankfurt. The formula should read the information in cell I and put "yes" in cell J if one of the cells does not have "Frankfurt" in it. Please ask if you have any questions.

Thanks

Here the copy of the sheet:

cell I cell H
[TABLE="width: 361"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Related Contacts Mailing Address State/Province[/TD]
[TD]Firm Contact (known by offices other than Frankfurt)[/TD]
[/TR]
[TR]
[TD]Frankfurt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frankfurt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD](blank)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frankfurt[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In J2 enter and copy down:

=1-(I2="Frankfurt")

A 1 as result means Firm contact is not Frankfurt, a 0 means Frankfurt.

Is this what you need?

 

[Bilabu1]

Looks good. Now how do I get "yes" or "no" in cell H based on that?

In J2 enter and copy down:

=1-(I2="Frankfurt")

A 1 as result means Firm contact is not Frankfurt, a 0 means Frankfurt.

Is this what you need?

 

[Aladin Akyurek]

Looks good. Now how do I get "yes" or "no" in cell H based on that?

The formula

=1-(I2="Frankfurt")

gives you a 1 per record if it's not Frankfurt. Are you wanting a yes per record instead if it's not Frankfurt?
​