Identify blank cell excluding Formula entered

[gtd526]

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
2	reverse
3	SPRD
4	-5
5
6
7	-6
8
9
10
11
12
13
14
15
16
17
18
19
20
21	FALSE
22	FALSE
Playoff Favs

Cell Formulas
Range	Formula
E4:E19	E4	=IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$D$5:$D$28,,Favs!$H$5:$H$28),2,0),"")
E21	E21	=IF(LEN($E$5)=0,TRUE,FALSE)
E22	E22	=IF(ISBLANK($E$5),TRUE,FALSE)

 

[jasonb75]

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]

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]

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),"")