Previous Topic Next Topic

#N/A help

Solved/Closed
PK - Sep 14, 2010 at 02:56 PM
 PK - Sep 15, 2010 at 10:29 AM
Hello,

This is what I want to ask: =if(c2=vlookup(c2,sheet1!b2:d17,1,false),c2,if(c2=vlookup(c2,sheet2!c2:e16,1,false),c2,"Not Found") -stop- Text wise here is how it's to go: check sheet 1 to see if number at c2 is there, if so disply c2, if not, check sheet 2 for c2, if so display c2 if not enter "Not Found". Even more compact: if it's on one sheet or the other return the number, if not return "Not Found". What returns is the ever popular #N/A. I have tried everything I know including ISNA, INERROR, MATCH, if I don't get #N/A, then it fails to locate the number at all on either sheet and returns a "Not Found". I have used this entry many times before with success but never got the #N/A which I know is what is screwing things up. HELP!!! PLEASE!!! Thank you!!

2 responses

Answer 1 / 2
RayH
Sep 14, 2010 at 03:46 PM
=IF(C2=IF(ISNA(VLOOKUP(C2,Sheet1!B2:D17,1,FALSE)),"",C2),C2,IF(C2=IF(ISNA(VLOOKUP(C2,Sheet2!C2:E16,1,FALSE)),"",C2),C2,"Not Found"))
0
Answer 2 / 2
PK
Sep 15, 2010 at 10:29 AM
RayH,

Thank you immensely, but this formula suffers from the same affliction my formulas have; it will not read the second function of the formula. It reads the first function fine, and even returns the correct solution. But, it will not read the second function of the formula at all. The IF/ISNA returns a True or False, if an "#N/A" is detected. Now, this is the tricky part, True means the formula was NOT satisfied and should display "Not Found". False means the formula WAS satisfied and the content of C2 should be displayed. If the first function returns a True (#N/A detected), test the second function. If either function returns a False, display C2. If both show True (#N/A detected in both functions), show "Not Found". Your formula got me thinking on the right track and I think I'm close with:

=IF((IF(ISNA(VLOOKUP(c2,'consolidated list'!B2:L21563,1,false))="N/A")),if((isna(vlookup(c2,assigned!C2:O10950,1,false)))="N/A")),"Not Found",c500)

I believe my nesting is out of order though, and it keeps showing "INVALID" in the Function Arguments. Can you check it out and if I have not confused you to insanity, please let me know what you discover. Thank you!
0