Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Contains formula

Hi,

In a cell i could have different words

I want an if statement to say

If cell AF2 contains these three words
"Awaiting Booking Amendment"

Return the text

Awaiting Booking Amendment

This will be a multiple ie as i have three or four different combos of words im searching for.

I.E

Cleared Today
Open Investigation

Thanks
Seamus
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seamus2626

ASKER

Hey Rory,

There could be other text in the cell and the text "Awaiting Booking Amendment" could be mispelt.

There wont be multiple matches within the cell

There currently stored elsewhere but i could definately make a list some where e.g. BA2 to BA12

Thanks
Seamus
If it could be misspelled, then you are out of luck. Best you could get would be some sort of fuzzy matching function (and I don't have time to write one of those). The rest is fairly simple:

=LOOKUP(2,1/(ISNUMBER(SEARCH($BA$2:$BA$12,A1))),$BA$2:$BA$12)

I might have to look into getting one of them sorted.

Thanks guys
Seamus