Identify blank cell excluding Formula entered

gtd526

Active Member
Joined
Jul 30, 2013
Messages
261
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2013
Messages
261
Office Version
  1. 2019
Platform
  1. Windows
What does =LEN(E5) tell you?

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
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),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,983
Messages
5,575,374
Members
412,658
Latest member
LS0009
Top