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.



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 •

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 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()):


Report •

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

Report •
Related Solutions

Ask Question