D dneas New Member Joined Mar 3, 2002 Messages 34 Nov 29, 2005 #1 Do you have to use another value to get the formula to give you a "0" if the vlookup is not false? =IF(VLOOKUP(B5,'[2004 PO''s.xls]PPV'!$A$5:$B$567,2,FALSE)="#N/A","0",VLOOKUP(B5,'[2004 PO"s.xls]PPV'!$A$5:$B$567,2,FALSE))
Do you have to use another value to get the formula to give you a "0" if the vlookup is not false? =IF(VLOOKUP(B5,'[2004 PO''s.xls]PPV'!$A$5:$B$567,2,FALSE)="#N/A","0",VLOOKUP(B5,'[2004 PO"s.xls]PPV'!$A$5:$B$567,2,FALSE))
L Legacy 51064 Guest Nov 29, 2005 #2 You have to have an answer for the entry. If you use "", the answe will be blank in the cell.
barry houdini MrExcel MVP Joined Mar 23, 2005 Messages 20,825 Nov 29, 2005 #3 You're on the right track but you can't quite do it that way, try this =IF(ISNA(VLOOKUP(B5,'[2004 PO''s.xls]PPV'!$A$5:$B$567,2,FALSE)),0,VLOOKUP(B5,'[2004 PO"s.xls]PPV'!$A$5:$B$567,2,FALSE))
You're on the right track but you can't quite do it that way, try this =IF(ISNA(VLOOKUP(B5,'[2004 PO''s.xls]PPV'!$A$5:$B$567,2,FALSE)),0,VLOOKUP(B5,'[2004 PO"s.xls]PPV'!$A$5:$B$567,2,FALSE))
D dneas New Member Joined Mar 3, 2002 Messages 34 Nov 29, 2005 #4 When I use your formula with the ISNA the non-matches are OK but the matches still give a #n/a for some reason?
When I use your formula with the ISNA the non-matches are OK but the matches still give a #n/a for some reason?
barry houdini MrExcel MVP Joined Mar 23, 2005 Messages 20,825 Nov 29, 2005 #5 ADVERTISEMENT I'm not sure how that would happen, have you modified the formula at all, it should work as per this exampleBook1ABCDEF34lookupresult5bananas2lemons46grapes3bananas27lemons4grapefruit08kumquat59Sheet1 Formula in E5 copied down column =IF(ISNA(VLOOKUP(D5,$A$5:$B$567,2,0)),0,VLOOKUP(D5,$A$5:$B$567,2,0))
ADVERTISEMENT I'm not sure how that would happen, have you modified the formula at all, it should work as per this exampleBook1ABCDEF34lookupresult5bananas2lemons46grapes3bananas27lemons4grapefruit08kumquat59Sheet1 Formula in E5 copied down column =IF(ISNA(VLOOKUP(D5,$A$5:$B$567,2,0)),0,VLOOKUP(D5,$A$5:$B$567,2,0))
D dneas New Member Joined Mar 3, 2002 Messages 34 Nov 29, 2005 #6 Thank you It worked when I changed the first lookup to 0 and the second one to false. Thank you very much for your help. =IF(ISNA(VLOOKUP(B5,Sheet2!$B$2:$C$564,2,0)),0,VLOOKUP(Sheet1!B5,Sheet2!$B$2:$C$564,2,FALSE))
Thank you It worked when I changed the first lookup to 0 and the second one to false. Thank you very much for your help. =IF(ISNA(VLOOKUP(B5,Sheet2!$B$2:$C$564,2,0)),0,VLOOKUP(Sheet1!B5,Sheet2!$B$2:$C$564,2,FALSE))
barry houdini MrExcel MVP Joined Mar 23, 2005 Messages 20,825 Nov 29, 2005 #7 FALSE or 0 for the 4th Argument of a VLOOKUP should be equivalent, switching one for the other should make no difference....but I'm happy that you have it working.....
FALSE or 0 for the 4th Argument of a VLOOKUP should be equivalent, switching one for the other should make no difference....but I'm happy that you have it working.....