| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why Different results
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why Different results
=IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1])))
Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why Different results
I think it should be
=IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) "Dave F" wrote: =IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why Different results
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. "bj" wrote: I think it should be =IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) "Dave F" wrote: =IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why Different results
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 "Dave F" wrote: 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. "bj" wrote: I think it should be =IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) "Dave F" wrote: =IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why Different results
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 "bj" wrote: I think it should be =IF(and(ISERROR(VLOOKUP([vlookup1]),iserror(VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) "Dave F" wrote: =IF(ISERROR(OR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2])),"",IF(ISERROR(VLOOKUP([vlookup1]),VLOOKUP([vlookup2]),VLOOKUP([vlookup1]))) Dave -- Brevity is the soul of wit. "Wanna Learn" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup on pivot table results = #N/A | Excel Worksheet Functions | |||
| How can I output VBA function results (array's for example) to spreadsheet | Excel Worksheet Functions | |||
| Returning Numeric Results across a Single Row in Consecutive Cells | Excel Worksheet Functions | |||
| abdualmohsn | Excel Discussion (Misc queries) | |||
| Date stamp formula results? Tricky problem? | Excel Discussion (Misc queries) | |||
Linear Mode
