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

Report •


#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


Ask Question