3

ISBLANK detects blank cells, but returns FALSE for cells with formulae. I need to detect blank cells that has been looked up through VLOOKUP.

Example, A1 .. F7:

1  foo     =isblank(b1)  =vlookup(a1,a$1:b$9,2)  =isblank(d1)
2  a       =isblank(b2)  =vlookup(a2,a$1:b$9,2)  =isblank(d2)
3  0       =isblank(b3)  =vlookup(a3,a$1:b$9,2)  =isblank(d3)
4  42      =isblank(b4)  =vlookup(a4,a$1:b$9,2)  =isblank(d4)
5  ="bar"  =isblank(b5)  =vlookup(a5,a$1:b$9,2)  =isblank(d5)
6  =""     =isblank(b6)  =vlookup(a6,a$1:b$9,2)  =isblank(d6)
7          =isblank(b7)  =vlookup(a7,a$1:b$9,2)  =isblank(d7)

Results:

1  foo     FALSE         foo                     FALSE
2  a       FALSE         a                       FALSE
3  0       FALSE         0                       FALSE
4  42      FALSE         42                      FALSE
5  ="bar"  FALSE         bar                     FALSE
6  =""     FALSE                                 FALSE
7          TRUE                                  FALSE

I tried a workaround with LEN, but that will return 1 for blank cells (blank evaluates numerically to 0 and LEN converts this to text before checking length). There is also the problem in row 6 where LEN (correctly) returns 0.

Is there a way?

I am primarily using openoffice and libreoffice, but this should also apply to excel.

Lafexlos
  • 7,618
  • 5
  • 38
  • 53
Trygve Flathen
  • 686
  • 7
  • 15

1 Answers1

0

If you need something like =VLOOKUP() that can also find blanks, consider using MATCH and INDEX.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 2
    I am not trying to find blanks. I am checking if a given cell is blank, or rather if the value _returned from_ e.g. VLOOKUP is a blank. I could get ISBLANK to check the original cell by using MATCH and INDEX, but that would be rather painful compared to checking my VLOOKUP-column, especially since I use VLOOKUP to bring in data from another sheet here. – Trygve Flathen May 11 '14 at 14:34