How can I have my formula show a blank if searched cell is empty?

0
votes

I have cell '$A' searching cell'$N'. Cell '$N' contains a VLOOKUP that pulls a few tags from another data sheet. '$A' searches for two words "CS" & "IG". I need my formula for '$A' to show a blank cell if '$N' has pulled no data.

My formula for '$A' =IFERROR(IF(SEARCH("CS",$N20),"CS"),"IG")

edit* Formula found in '$N' =IFERROR(VLOOKUP($F2,Data!$A:$J,9,FALSE), " ")

The A column should show CS, IG, or just be left blank.

2
excelif-statementsearch
So, you want A to show "CS" if "CS" is present in N. And you want A to show "IG" if "IG" is present in N. And you want A to be blank if neither are found in N. What should A show if both "CS" AND "IG" are found in N? Or is that not possible? – Gravitate
CS or IG will always be present in the cell N if that cell has pulled data. CS & IG will never be present together. If the cell N has not pulled any data it will be blank, and that needs to be reflected in cell A as a blank. – Ryan

2 Answers

1
votes

Ok. Based on the assumptions below taken from your comments:

  • "IG" & "CS" well never appear together in column N.

  • Column N will always contain "IG" OR contain "CS" OR be blank.

Try this:

enter image description here

=IF($N1="","",IF(ISERROR(SEARCH("CS",$N1)),"IG","CS"))

How this formula works:

  1. In col N is blank, then return blank.
  2. If col N is not blank, check if the search for "CS" in col N returns an error.
  3. If an error is returned, that means col N does not contain "CS", and therefore, returns "IG".
  4. If the search does not return an error, it means that "CS" was found and that is what should be returned.

If you need any more clarification, just let me know.

EDIT: AS AN ASIDE

In your comments, you said that in column N, you have this formula:

=IFERROR(VLOOKUP($F20,Data!$A:$J,9,FALSE), 0)

I can see a few issues with this:

  1. As you are returning column 9, that is column I, so your range could be Data!$A:$I (I am not sure but, as it is a smaller range, it may calculate quicker).
  2. If a value is not found, it will return zero, not blank, which is what my formula above is checking for.
  3. You should consider using an INDEX/MATCH formula instead, as it is much quicker and more versatile.

Please see my suggested formula below:

=IF(INDEX(Data!$I:$I,MATCH($F20,Data!$A:$A,0))="","",INDEX(Data!$I:$I,MATCH($F20,Data!$A:$A,0)))
0
votes

You need to modify you code to reflect this...

=IFERROR(IF(SEARCH("CS",$N20),"CS", "IG"),"")

If looks like you may have forgotten to put the 'false' statement in the formula function.

You can also try this...

=If(Search("CS",$N20), "CS", if(Search("IG", $N20), "IG", ""))