IF Error, if empty, vlookup

[Anto_BT]

Hello,

I have in one sheet table that i need to get number with vlookup, but in some it is showing me error in some just empty. I need if it is error or empty then to be "0".

Thank you

[cheeze83]

Hi, try something like:

Please Login or Register  to view this content.

[Anto_BT]

It is the same, where is empty it is showing empty and I need to be "0"

Thank you

[cheeze83]

Are you able to upload a sample of your sheet?

[Anto_BT]

In Planerfolgsrechnungen column "G" you have for Januar.
You need to take the numbers from "Input 1" column "F" and i don't need empty cells in Planerfolgsrechnungen

Thank you

[XOR LX]

Hi,

You do not have any errors in that column. And the "blanks" in that column are not as the result of the VLOOKUP, but rather due to the first condition:

=IF(LEN(A14)=4,IF(ISERROR((VLOOKUP($A14,'Input 01'!$A$1:$F$997,5,0))-(VLOOKUP($A14,'Input 01'!$A$1:$F$997,4,0))),0,(VLOOKUP($A14,'Input 01'!$A$1:$F$997,5,0))-(VLOOKUP($A14,'Input 01'!$A$1:$F$997,4,0))),"")

being FALSE.

Change your "" to a 0 if that is what you wish.

Regards

[Anto_BT]

But if you go with this formula down, and eg. Konto in column A "9910" it is "0" but in input 1 is 1,436.80>

Regards

[XOR LX]

That value's in column 4 of Input 01 - your formula is looking, initially, in column 5.

=IF(LEN(A166)=4,IF(ISERROR((VLOOKUP($A166,'Input 01'!$A$1:$F$997,5,0))-(VLOOKUP($A166,'Input 01'!$A$1:$F$997,4,0))),0,(VLOOKUP($A166,'Input 01'!$A$1:$F$997,5,0))-(VLOOKUP($A166,'Input 01'!$A$1:$F$997,4,0))),"")

And the result of "" minus 1436.80 is, of course, an error; hence your result.

Regards

[Anto_BT]

Not working like I want. I don't know why...

[XOR LX]

What do you want?

[Anto_BT]

serviceplan Entwurf reporting 2013 V1.1 - Copy.xlsx

Here is something i'v done, i need to function like that in column G but now the problem is the #VALUE! in columnD and columnE. Some solution?

[XOR LX]

Hi,

In cell D9 of the Planerfolgsrechnungen tab, you have a #VALUE! error. The reason for this is that the formula in that cell:

=IF(LEN(A9)=4,G9+J9+M9+P9+S9+V9+Y9+AB9+AE9+AH9+AK9+AN9,"")

equates to:

=IF(TRUE," "+0+0+0+0+0+0+0+0+0+0+0,"")

and, since addition of textual and numerical values is not defined in Excel, it is clear that you need to do something about this " " return.

Since this is, in turn, obtained from cell G9, which contains the formula:

=IF(ISNA(VLOOKUP($A9,'Input 01'!$A$1:$F$997,6,FALSE)),0,IF(LEN(A9)=4,(VLOOKUP($A9,'Input 01'!$A$1:$F$997,6,FALSE)),0))

which results in:

=IF(ISNA(" "),0,IF(TRUE,(" "),0))

which, since " " is not equal to the error value #N/A, returns " ".

This, then, is your main problem: the cells which seem to be blank in the Input 01 tab are not actually blank - the entry corresponding to Konto=3005 in the Schlussbilanz is actually " ", i.e. it contains an extra space.

I suggest you do a clean up of your data in the Input tabs, and make sure that 'blank' cells truly are blank.

If, for example, you delete the extra space in cell F44 of the Input 01 tab, then you will find that your formula in G9 of the Planerfolgsrechnungen tab will work as desired, since it will then be:

=IF(ISNA(""),0,IF(TRUE,(""),0))

which equates to:

=""

for which Excel will return 0 (since it cannot 'return' "" in a cell).

Regards

[Anto_BT]

Tank you very much.

[XOR LX]

You're welcome.