Blank cell error

Good morning everyone.

my cell formula is in cell D2 which is; lookup(C2,A2:B32)

when C2 is blank an error (red Triangle) appears in D2

how do I resolve this error?

Cheers

iMac 24″, macOS 15.0

Posted on Oct 1, 2024 4:30 PM

Top-ranking reply

Badunit

Level 7

31,331 points

Posted on Oct 1, 2024 4:50 PM

It will give an error if it cannot find a match. It will not find a match for a blank cell.

You can use XLOOKUP which allows you to provide a value (or the null string "") when a match is not found. Or you can put the IFERROR function around your current formula, but that would be silly. LOOKUP is an old function. XLOOKUP is much better.

View in context

Similar questions

The operator “ ” expects a number, date or duration, but cell IN 19/09/2023 contains a string The operator “ ” expects a number, date or duration, but cell IN 19/09/2023 contains a string Can anyone help? Because this is driving me nuts. The above is the error message I get after I've copied and pasted a working formula into the next cell down. The formula has worked flawlessly for 174 lines (rows). Now, suddenly, I'm getting this error. I've checked back on this community, and there have been several instances of this happening to others, with mixed results - some have solved it, others haven't. The formula is not exactly complicated - it's just taking a starting number in one cell and subtracting the number in one cell and adding the cell in another. A simple case of A-B+C= whatever. I'm using it as a very simple accounting tool. If I leave a cell blank, that means the value is zero. I've made sure that the numerical columns have been formatted as a number with 2 decimal places. It appears that, inexplicably, Numbers now thinks the blank cell contains text. It does not - it does not contain anything, it is completely blank! The workaround is for me to simply insert a zero into the blank cell, then the formula works. But why is that suddenly necessary, when it has happily worked with blank cells for 174 lines?? Any assistance/suggestions appreciated... Thanks.
977 3

2 replies

Top-ranking reply

Badunit

Level 7

31,331 points

Oct 1, 2024 4:50 PM in response to Sparviero70

It will give an error if it cannot find a match. It will not find a match for a blank cell.

Sparviero70 Author

Level 1

4 points

Oct 1, 2024 5:04 PM in response to Badunit

Thank you for the reply, I'll see if I can make this work.

Cheers

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Blank cell error