IF ELSE in excel -- check if a cell is null/empty

Question

I am not programming in VBA. This is a simple excel spreadsheet. Essentially, I have a formula the finds the lowest price in a range of cells and returns its respective column title. It works fine. However, some ranges do not have any values at all. So, the cell that populates with the column header displays #N/A in this case.

What I need to do is check whether or not this happens. If the result is #N/A, I want the cell to show "No Values to Reference." I was thinking something along the lines of:

=IF(CELL != NULL, Display_Header, ELSE "No Bids to Reference")

OR

=IF(CELL = NULL, No Bids to Reference", ELSE Display_Header)

I have tried both NULL and #N/A but I am not sure how Excel handles these situations. That is, how Excel handles #N/A returns.

IF(INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0))= "#N/A","No Bids To Reference", INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0)))

See: stackoverflow.com/questions/1119614/… and excel.tips.net/T002814_Returning_a_Blank_Value.html - this should be enough information for you to work backwards from for a solution — ɴᴀᴛᴇ ᴅᴇᴠ
– ɴᴀᴛᴇ ᴅᴇᴠ, Commented Jun 12, 2013 at 14:04
first thing would be to forget all the syntax you know about if clauses, cause Excel doesn't use that kind of syntax, but its own crappy syntax. Only my point of view but still... — Laurent S.
– Laurent S., Commented Jun 12, 2013 at 14:06

Jerry · Accepted Answer · 2013-06-12 14:55:23Z

8

Since you have only one calculation here, you can simply use an IFERROR:

=IFERROR(INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0)),"No Bids To Reference")

The IFERROR checks whether the expression evaluates to an error (it can be #N/A, #VALUE! or #REF!). If true, evaluate the next expression, otherwise return the first expression.

edited Jun 12, 2013 at 14:55

answered Jun 12, 2013 at 14:05

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jerry Over a year ago

@user2140261 Thank you for the +1. I'd up your answer if it were there ^^

sherrellbc Over a year ago

Worked perfectly +1. I have another question if you are still around. Is there anything wrong with this formula? The result is zero if CELL =! 0 and it is less.

Jerry Over a year ago

@sherrellbc I'm not sure I understand you on that point... :s. The IFERROR checks whether the expression evaluates to an error (it can be #N/A, #VALUE! or #REF!). If true, evaluate the next expression, otherwise return the first expression.

sherrellbc Over a year ago

Sorry. I had posted a formula I was having trouble with just below my comment. I figured out what I was doing wrong and deleted the comment by forgot about the one above. All is well.

Jerry Over a year ago

@sherrellbc Oh, okay. That's fine :)

Andrew · Accepted Answer · 2014-02-19 20:28:52Z

2

I would CTL+G, Special, and replace the values of all cells with formula errors.

answered Feb 19, 2014 at 20:28

Andrew

211 bronze badge

Collectives™ on Stack Overflow

IF ELSE in excel -- check if a cell is null/empty

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related