0

I have made a formula that basically looks for a string of text inside a cell and if it finds it puts it in another cell. I needed to check three cells so I did three separate formulas (because I'm useless at embedding the formulas).

So now I have three cells with the information in them. All of the information is the same. It's basically if you find the word diamond write diamond. So now in my three columns some are empty and some have the word diamond (or another gem). I need to find a way to make a final column which takes all three columns and if not blank puts the answer in the final column.

It is something along the lines of:

A1 is something like "I like diamonds"
B1 is "category: diamonds from the far east"
C1 is something like "diamonds from the far east are wonderful"

I've got three formulas (D1, E1, F1) which search for the string in A1, B1, C1 individually, and return the string if they find it. That part works.

Now I want G1 to search D1, E1, F1 and if they're not blank then return the string. My IF statement works, but if the final column is blank it returns blank. That's why I tried to do an OR statement combined with IF.

I tried to do this

=IF(NOT(ISBLANK(BI2)),BI2,IF(NOT(ISBLANK(BH2)),BH2,IF(NOT(ISBLANK(BG2)),BG2,"")))

It doesn't work though. I think I need an OR statement. So I tried to make one:

=OR(IF(NOT(ISBLANK(BG2)),""),(IF(NOT(ISBLANK(BH2)),BH2,""),IF(NOT(ISBLANK(BI2)),BI2,"")))

But that didn't work either. Can anyone more intelligent than I help me out?

  • I find it hard to understand what you are looking for. Your formula does not 'look for a string of text inside a cell', it only checks whether it's blank or not. Next, what if the three cells are not blank and contain different words? What word should be taken to the final column? – Jerry Jul 5 '14 at 12:22
  • Edit your question please. – Jerry Jul 5 '14 at 12:53
  • 1
    Thank you, and no worries, it takes some time to get used to how the stackexchange sites work :) – Jerry Jul 5 '14 at 13:13
1

The problem with your first formula is that ISBLANK() does not consider cells with formulas as being blank (even if the result of the formula is blank). One way to workaround this is to use ="" (or <>"" for not blank):

=IF(BI2<>"",BI2,IF(BH2<>"",BH2,IF(BG2<>"",BG2,"")))

Or you could use something like this:

=IFERROR(HLOOKUP("diamond",BG2:BI2,1,0),"")

Since you already know the word, just put it there. The above will return the same result as the first formula. It looks for diamond in the three cells and if not found, you get an error which is handled by IFERROR and get a blank.

  • Thanks! I used the first one because the word is not always Diamond. I really appreciate your help. – Eoin Jul 7 '14 at 10:23
  • @EoinOliver Ok, great! :) – Jerry Jul 7 '14 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.