There are times when we look at a cell and what we see is
not what really is in the cell.
Take a look at the below example.
Value

Test

Formula

A

FALSE

=ISBLANK(A2)

B

FALSE

=ISBLANK(A3)

1

FALSE

=ISBLANK(A4)

2

FALSE

=ISBLANK(A5)


FALSE

=ISBLANK(A6)


TRUE

=ISBLANK(A7)

I want to test the values in my left column (column A) to
see if there is a value in the cell or if the cell is empty. I can use the
formula =ISBLANK to accomplish this.
As you can see the first 4 rows all have values in them and
the formula returns False indicating that. However in the last two cells I get
different results. Both cells appear to be empty but in reality only one of
them is as indicated by the TRUE result.
Now there are a couple of reasons why a seemingly empty cell
would return FALSE for the =isblank function.
First is that there is a value in the cell. Perhaps the font
color of the text in the cell matches the background color of the cell giving
the appearance of an empty cell.
The second and more common reason is that there is a space
value in the cell (someone has hit the space bar and then the enter key. This
also can occur when importing values from other data sources such as an Access
database.
Take another look at the data. This time I want to do a
VLookup for the letter B in my column A and return the value in column B.
However my VLookup formula does not work. The reason is again due to a blank
space after the B in cell A3. The lookup is searching for “B” and the value in
the cell is “B “.
Value

Test

Vlookup

Formula

A

FALSE

#N/A

=VLOOKUP("B",A1:B7,2,FALSE)

B

FALSE



1

FALSE



2

FALSE




FALSE




TRUE



This vlookup error is quite common. Almost weekly I am asked
to trouble shoot a coworkers worksheet because their vlookup does not work. 9
out of 10 times the problem lies with blank spaces to the right of the value(s)
they are using in the look up.
Now you can avoid this common error by using the =Trim
function to strip out blanks from both sides of your data.
A third but common occurrence is when a number is
represented as a text. Again if we look at the data in column A, I show two
numbers (1 and 2). However in this instance, 1 is not a number but a text. The
formula ISText fails for the number 2 since 2 is a number in my data.
Value

Blank Test

Vlookup

Formula

Text Value

Formula

A

FALSE

#N/A

=VLOOKUP("B",A1:B7,2,FALSE)

TRUE


B

FALSE



TRUE


1

FALSE



TRUE

=ISTEXT(A4)

2

FALSE



FALSE

=ISTEXT(A5)


FALSE



TRUE



TRUE



FALSE


So what does all this mean? Well just because it looks like
a duck and quacks like a duck, it does not mean it is a duck. So when you have
a formula that you are sure should work. Start by taking a closer look at your
data to ensure that what you see is what you really have.
No comments:
Post a Comment