## If cell has value

The easiest way to check if a cell has a value is, in my opinion, to use the equal sign to compare the cell value with the value you are looking for.

The equal sign is a logical operator that turns the formula a logical expression and returns a boolean value, TRUE or FALSE.

Formula in cell C3:

Use double quotation marks before and after the text value in order to do the comparison. B3 is a cell reference to a specific cell at the intersection of column B and row 3.

Copy cell C3 and paste to cells below and the cell reference changes in each cell. B3 is, therefore, a relative cell reference.

If you forget the double quotation marks Excel will evaluate your text value as a built-in function or a user-defined function. If such function can't be found Excel returns #NAME? error.

Formula in cell D8:

Don't use double quotation marks with numbers, if you do Excel will evaluate your number as a text value and the comparison will never return TRUE.

Example,

returns FALSE even though cell B10 has number 7.

The comparison made by the equal sign is, however, not case-sensitive. Use the EXACT function to perform a case-sensitive comparison, see picture above.

A formula like this one has one of the values hardcoded into the formula:

If you want to compare another value you must change each formula in every cell which is not at all efficient.

The picture above shows a formula that compares two cell values, however, one of the cell references are locked to cell C9.

How did I enter this formula?

- Double click on cell C3 with left mouse button.
- Type =B3=C9
- Press function key F4 to automatically change C9 to $C$9.
- Press Enter
- Copy cell C3
- Paste to cells below.

If you now examine the formula you will see that the first cell reference B3 in the formula changes in each cell.

This happens with relative cell reference whereas an absolute cell reference doesn't change.

Having cell references in your formulas allows you to quickly change a value without adjusting the cell formulas at all.

Try it yourself, change the value in cell C9 to Banana and check column C for changes. Cell C6 is now TRUE and C5 is FALSE.

The equal sign (=) is one out of 3 different logical operators, the picture above shows you the *greater than* sign (>).

You can combine these three logical operators to perform different logical expressions, see table below.

Logical operator |
Description |
Example |

< | less than | B3<$C$9 |

> | greater than | B3>$C$9 |

<= | less than or equal to | B3<=$C$9 |

>= | greater than or equal to | B3>=$C$9 |

= | equal to | B3=$C$9 |

<> | not equal to | B3<>$C$9 |

The formula in column C returns TRUE if the value in column B is larger than 4.

Now you know what a logical expression is which is an essential part of an IF function.

### Download Excel *.xlsx file

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are [โฆ]

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3. Related articles

Use IF + COUNTIF to perform numerous conditions

The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF [โฆ]

IF with AND function โ multiple conditions

The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. [โฆ]

The picture above shows different values in column B and a formula in column C that tries to identifies the [โฆ]

If cell equals value from list

Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The COUNTIF function counts how many values in E3:E5 match cell B3, it returns [โฆ]

If cell contains text from list

The array formula in cell C3 checks if cell B3 contains at least one of the values in List (E3:E7), [โฆ]

The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and [โฆ]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form