Formul ? ,0),"")

[zone709]

Hi using this formula if nothing found return 0

I would like to change it if it finds nothing to return blank not 0

=IFERROR(VLOOKUP($H$3:$H$100, Data!$D$2:$AH$100,25,0),"")

Thanks

 

[Joe4]

See the "VLOOKUP return blank instead of 0" section here: https://www.extendoffice.com/documents/excel/2447-excel-vlookup-blank-instead-of-0-n-a.html

 

[Aladin Akyurek]

What does this VLOOKUP return if successful - number or text?

 

[zone709]

=IF(LEN(VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))="0",VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))

returns FALSE I also did =0"" but it doesn't work either

 

[zone709]

​number

 

[Joe4]

Why are you putting text quotes around the 0?

=IF(LEN(VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))="0",VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))

That is not what that formula in that link shows.
The length (LEN) of anything has to be a number, not text (anything enclosed in double-quotes is literal text).

 

[zone709]

I followed the link you sent me. Actually know it asks me if I wanted a correction on it and I hit yes. Then it put it like that "0"

 

[zone709]

=IF(LEN(VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))=0,VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))

This retunrs 0

 

[Joe4]

I followed the link you sent me. Its in the link if I should use it or not I didn't know why I'm asking for help

You did not follow it carefully.

This is the formula they show:
=IF(LEN(VLOOKUP(E1,A2:B10,2,0))=0,"",VLOOKUP(E1,A2:B10,2,0))

This is your formula:
=IF(LEN(VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))="0",VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))

Notice the differences in red.
Also, when doing a VLOOKUP, your first value should be a single cell or value, not an entire range.
See: https://exceljet.net/excel-functions/excel-vlookup-function

 

[Joe4]

=IF(LEN(VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))=0,VLOOKUP($H$3:$H$100,Data!$D$2:$AH$100,3,0))

This retunrs 0

.
That is still missing the argument after the zero.

If looking up H3, it should look like this:

[COLOR=#333333]=IF(LEN(VLOOKUP([/COLOR][COLOR=#ff0000]$H3[/COLOR][COLOR=#333333],Data!$D$2:$AH$100,3,0))=0[/COLOR][COLOR=#ff0000],""[/COLOR][COLOR=#333333],VLOOKUP([/COLOR][COLOR=#ff0000]$H3[/COLOR][COLOR=#333333],Data!$D$2:$AH$100,3,0))[/COLOR]