# Help with IF/OR/AND? Formula

1. ## Help with IF/OR/AND? Formula

Hi guys,

I am currently running a spreadsheet which has an IF statement which also uses ISNA & MATCH. The formula is working fine bit i would like to adapt it slightly to include a couple of other statements - I thought it would be quite a simple one at first with a nested IF statement but am having difficulties with it - wondering if any of you can help?

I have attached a simplified version of my sheet.

I would like column C to display TBC or YES depending on the conditions.

As it is the column will display YES if the name in column A matches one of the names in column E.

However i would also like C to display YES if the Role column (B) is populated (regardless of whether the corresponding name in A is in E) with any text UNLESS it says "Hunter".

So.....

If A is in the the range E:E then C = YES OR if B = any text (with the exception of "Hunter") then C = YES.

If A is NOT in the range E:E AND B = blank or "Hunter" then C = TBC.

I hope that makes sense!

In the sheet attached the results for C2 to C21 should come out as :

C2 YES
C3 YES
C4 YES
C5 TBC
C6 YES
C7 YES
C8 YES
C9 TBC
C10 YES
C11 YES
C12 TBC
C13 TBC
C14 TBC
C15 TBC
C16 YES
C17 TBC
C18 TBC
C19 YES
C20 YES
C21 TBC

Any help would be most appreciated!

Richard

2. ## Re: Help with IF/OR/AND? Formula

Hi,

an attempt in C2 and down:

``Please Login or Register  to view this content.``
Regards

3. ## Re: Help with IF/OR/AND? Formula

hi bellpiero, you may try:
``Please Login or Register  to view this content.``

4. ## Re: Help with IF/OR/AND? Formula

Hi both,

Thanks very much for your responses. They have almost solved the issue but there is one small discrepancy. I may not have been too clear so apologies for that!

If the name in col A is in the E:E range then i would like the Status to be YES regardless of whether B was "Hunter" or not.

So the two formulas provided work except for when a name is added that is in the names database and is "Hunter" in col B. An example of this would be adding the following into row 22:

A22: Mark, B22: Hunter.

I would need C22 to become YES but with these formulas it is TBC. Basically if the name is in the E:E range it should override anything else and result as YES.

Regards

5. ## Re: Help with IF/OR/AND? Formula

Ciao Bellpiero,

attached an example.

Maybe:

``Please Login or Register  to view this content.``
Regards

6. ## Re: Help with IF/OR/AND? Formula

Canapone,

Thank you! Works perfectly!

7. ## Re: Help with IF/OR/AND? Formula

Ciao,

Greetings from a humid and sticky Firenze

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1