# Identify blank cell excluding Formula entered

#### gtd526

##### Active Member
Hello,
I have a formula using a reverse vLookup that will might bring "" to the cell. I use ISBLANK or LEN=0 to identify blank cells, but its not working.
Here is the data:
NBA.xlsm
E
2reverse
3SPRD
4-5
5
6
7-6
8
9
10
11
12
13
14
15
16
17
18
19
20
21FALSE
22FALSE
Playoff Favs
Cell Formulas
RangeFormula
E4:E19E4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$D\$5:\$D\$28,,Favs!\$H\$5:\$H\$28),2,0),"")
E21E21=IF(LEN(\$E\$5)=0,TRUE,FALSE)
E22E22=IF(ISBLANK(\$E\$5),TRUE,FALSE)

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jasonb75

##### Well-known Member
The second formula should work if the formula in E5 is returning a blank using ""

What does =LEN(E5) tell you?

Best guess without seeing the Favs sheet is that the lookup is not resulting in an error and that the result returned is " " instead of ""

#### gtd526

##### Active Member
What does =LEN(E5) tell you?

The LEN(E5)=1
I made adjustments using LEN of 1 in the formula. Its all good now.

#### jasonb75

##### Well-known Member
LEN(E5)= 1 tells me that the result of the formula is not ""
As I said above, it is likely that the formula is not resulting in an error and that the other sheet has a space in the result cell.

Changing the formula to =IF(LEN(E5)=1 is only a workaround, it might be good for now but if the result is an error, or if the other sheet contains a mixture of spaces and proper blanks then it will stop working again.

It would be better to check the source of the vlookup results and remove the extra spaces. Often it comes to a point when there is no workaround and you have to go back and fix the source, which then means that your other workarounds all stop working. Better to spend 10 minutes doing it properly now than 2 hours later

Another observation on your formula, the result of CHOOSE 3 is empty, the last range is in CHOOSE 4 so is never seen, I'm not entirely sure what you're trying to do with that formula but I see no reason why you wouldn't be able to use a simple INDEX MATCH combination with just the first 2 ranges.

=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$D\$5:\$D\$28,,Favs!\$H\$5:\$H\$28),2,0),"")

Replies
10
Views
179
Replies
7
Views
47
Replies
1
Views
69
Replies
6
Views
58
Replies
8
Views
273