# Excel ISNA question Microsoft Excel 2007
January 27, 2010 at 06:57:56
Specs: Windows XP
 I have 3 sheets. Sheet 1 Has a list of part numbers. Sheet 2 has a list of variables for the part numbers, lets say Lead Time is the one we are working with. On Sheet 2 'All Parts data', the lead time is 55. On Sheet 3,'MITIGATION', I have a situation where a person will go in and validate the data. I want to say if they validate 2 sets to "yes", then it will return into sheet 1, the new lead time of 3, if they are not both Yes, then use the lead time from Sheet 2.Formula:=IF((VLOOKUP(\$B666,MITIGATION!\$D:\$M,10,FALSE))=(VLOOKUP(\$B666,MITIGATION!\$D:\$M,4,FALSE)),VLOOKUP(\$B666,MITIGATION!\$D:\$M,5,FALSE),VLOOKUP(\$B666,'All Parts Data'!\$A:\$N,14,FALSE))I am trying to get rid of the N/A, but I cannot figure out how to use ISNA in this formula. I am not an Excel whiz, i spend 3 hours trying to get this formula right (tried AND, OR, combinations, et c, before getting to this).If anyone could help, I would greatly appreciate it. See More: Excel ISNA question

#1 January 27, 2010 at 07:56:18
 The standard method for getting rid of #N/A from a VLOOKUP is to wrap the VLOOKUP in an IF(ISNA()) function:=IF(ISNA(myVLOOKUP),value_if_true,myVLOOKUP)If myVLOOKUP returns a #N/A, then the IF(ISNA()) is TRUE and the value_if_true will be returned. If myVLOOKUP actually finds a value, then the IF(ISNA()) will be FALSE and the second myVLOOKUP will be evaluated.Since I'm assuming you don't care which of your multiple VLOOKUP's fails, I wrapped your entire formula in the IF(ISNA()):=IF(ISNA(IF((VLOOKUP(\$B666,MITIGATION!\$D:\$M,10,FALSE))=(VLOOKUP(\$B666,MITIGATION!\$D:\$M,4,FALSE)),VLOOKUP(\$B666,MITIGATION!\$D:\$M,5,FALSE),VLOOKUP(\$B666,'ALL PARTS DATA'!\$A:\$N,14,FALSE))),"Value Not Found",IF((VLOOKUP(\$B666,MITIGATION!\$D:\$M,10,FALSE))=(VLOOKUP(\$B666,MITIGATION!\$D:\$M,4,FALSE)),VLOOKUP(\$B666,MITIGATION!\$D:\$M,5,FALSE),VLOOKUP(\$B666,'ALL PARTS DATA'!\$A:\$N,14,FALSE)))

Report •

#2
January 27, 2010 at 09:13:57
 Thanks! Seems to work well.

Report •
Related Solutions 