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.

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

myVLOOKUPreturns a #N/A, then the IF(ISNA()) is TRUE and thevalue_if_truewill be returned. IfmyVLOOKUPactually finds a value, then the IF(ISNA()) will be FALSE and the secondmyVLOOKUPwill 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)))

Thanks! Seems to work well.

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History