CODEWITHSUNDEEP
excelexcel-formula
Sheila
Sheila

Reputation: 37

Excel - IFERROR Function

Having some issues with my formula and I'm hoping to find some help here.

This is my formula:

=IFERROR(REPLACE(F7, FIND("BOB",A2), 13, "Other"),A2)

I have 3 names in my data (Column B):

What I want to do is find "Bob"'s name and in column C return it as "Other", and I want Sarah to return as Sarah and Lee to return as Lee.

With my formula, currently it's returning everyone's name as "Open" and I'm not quite sure why.

Any recommendations?

Upvotes: 1

Views: 221

Answers (2)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

An alternative to @ScottCraner's answer.

It is unclear if you want only "Bob" to result in "Other", or if you want anything other than "Sarah" or "Lee" to result in "Other".

If you want the latter, you can use this array formula:

= IF(MAX(IFERROR(FIND({"Sarah","Lee"},A2),0))=0,"Other",A2)

Note this is an array formula so you must press Ctrl+Shift+Enter on your keyboard after typing the formula rather than just Enter.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Use:

=IF(ISNUMBER(FIND("Bob",A2)),"Other",A2)

![enter image description here

one thing to remember FIND is an exact match while SEARCH does not care about case.

So if Bob can be BOB and Bob then you may want to use SEARCH

=IF(ISNUMBER(SEARCH("Bob",A2)),"Other",A2)

Upvotes: 2

Related Questions