Why Different results

[Guest]

Hello
THis is my formula
=IF(ISNA(IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP(D42,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE))),"",IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)))

This gives me the right answer but when there is no answer I get 3 different
results I get 0 or blank , (which is what I want) or #NA. Why three
different answers. What is wrong ? thanks

 

[Guest]

=IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

Dave

 

[Guest]

I think it should be
=IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

=IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

Dave
--
Brevity is the soul of wit.

Hello
THis is my formula
=IF(ISNA(IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP(D42,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE))),"",IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)))

This gives me the right answer but when there is no answer I get 3 different
results I get 0 or blank , (which is what I want) or #NA. Why three
different answers. What is wrong ? thanks

 

[Guest]

Why and? My understanding is that if VLOOKUP1 OR VLOOKUP2 return errors, he
wants a blank returned? Of course if that's incorrect, and the test is
whether BOTH vlookups return errors, then yes, AND is correct.

Dave
--
Brevity is the soul of wit.

I think it should be
=IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

=IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

Dave
--
Brevity is the soul of wit.

Hello
THis is my formula
=IF(ISNA(IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP(D42,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE))),"",IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)))

This gives me the right answer but when there is no answer I get 3 different
results I get 0 or blank , (which is what I want) or #NA. Why three
different answers. What is wrong ? thanks

 

[Guest]

His original isna() would only be true if both L1 and L2 returned errors
with your equation the "else" section would not need the iserror section
I still do not understand why he gets the three responses

Why and? My understanding is that if VLOOKUP1 OR VLOOKUP2 return errors, he
wants a blank returned? Of course if that's incorrect, and the test is
whether BOTH vlookups return errors, then yes, AND is correct.

Dave
--
Brevity is the soul of wit.

I think it should be
=IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

=IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

Dave
--
Brevity is the soul of wit.


:

Hello
THis is my formula
=IF(ISNA(IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP(D42,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE))),"",IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)))

This gives me the right answer but when there is no answer I get 3 different
results I get 0 or blank , (which is what I want) or #NA. Why three
different answers. What is wrong ? thanks

 

[Guest]

a simpler equation would be
=if(isna(Vlookup1)),if(isna(Vlookup2)),"",Vlookup2),Vlookup1)
If 1 and 2 could give different results, Priority for 1 and 2 must be
watched this form would respond for Vlookup 1 as apreference

I think it should be
=IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

=IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))

Dave
--
Brevity is the soul of wit.

Hello
THis is my formula
=IF(ISNA(IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP(D42,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE))),"",IF(ISNA(VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$B$1:$D$27061,3,FALSE),VLOOKUP($D2,'ALL JDE HARMONIZATION
CODES'!$A$1:$D$27061,4,FALSE)))

This gives me the right answer but when there is no answer I get 3 different
results I get 0 or blank , (which is what I want) or #NA. Why three
different answers. What is wrong ? thanks