Using If function to several times to get different string

[algebr]

Hello,

My problem is such. I have column values in a second worksheet that I am manipulating in the first worksheet. I am also using the find function, so this is what I ideally wish would work

=if(find("b", secondsheet!a2), "Found B", "Found C")

Unfortunately, the false part isn't printing and is giving me a !value error. I tried using ifs and ors but it isn't working. Help appreciated.

[oeldere]

You can use VLookup for that.

Make a table in the second sheet.

And use Vlookup to look in the table, and post the found value in sheet 1.

[algebr]

Okay, I will try Vlookup now.

I also tried this, =if(iserror(find("b", secondsheet!a2), "Found C", "Found B")

but that didn't work either.

EDIT: Vlookup doesn't do what I need.

[oeldere]

In that case, you better add an excel file, without confidentional information.

Also add the desired result.

[bebo021999]

May be this works:
=IF(ISNUMBER(SEARCH("b",A2)),"Found B","Found C")

[Tony Valko]

Not real clear what you're trying to do.

Based on your formula, if the cell does not contain a lower case "b" anywhere within the cell then FIND( ) will return the #VALUE! error.

For example, if the cell contains "Bill" then FIND( ) will have failed and the result will be the #VALUE! error.

Are you sure you want to use the FIND( ) function?

[algebr]

Tony,

My problem is to suppress the value error, how can I do that? I only have two values in that column, so I was thinking of some way to use iserror so that when find gives the error, the if conditional would assign it as a my other value. possible to do this?


EDIT: Got it!!!

The syntax was this, =IF(ISERROR(FIND("b", sheet2!A2)), "c", "b")

[Tony Valko]

Maybe this...

=IF(COUNTIF(secondsheet!A2,"*B*"),"Found B","Found C")

[algebr]

Actually, there is another problem. My formula works if there are only two values. how could I extend it to if there were several values but I only want to pull one out, provided there is at least some name recognition.

[Tony Valko]

I think we need a more detailed explanation of what you're wanting to do.

Provide examples of what's in the cell(s) and what result(s) you expect.

[vlady]

To attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[algebr]

Actually, thank you everyone! I figured it out and wrote it my edit. Also used a little VBA code.

[Tony Valko]

Good deal. Thanks for the feedback!

[vlady]

TO mark thread "Solved"

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.