@ DerbyDad03 "Even assuming that the 2 columns are sorted in the same order, as soon as Dx doesn't match it's corresponding AKx - meaning a part # is missing in AK, then all the remaining Dx's will not match their corresponding AKx's, even if the number is in both lists. D E AK 1 Same 1 2 Same 2 3 Different 4 4 Different 5 <-- Wrong Answer 5 Different <-- Wrong Answer" What are you smoking? When D=4 and AK=5 they are indeed different. When D=5 and AK=null they are indeed different. Why is that a wrong answer? @hr You have to align the comparison operators between the two fields that you are comparing. I did not intend for you to insert that exact code into your worksheet but use the concept as an example. An easier way for all of this is to import the sheet into a database table and write an "instance not found" query. Sheesh!

0

Here is what I am suggesting... First, let's begin with the assumption that you are simply trying to determine which of the part numbers in a given list is also in another. If that is not what you are trying to do, please give us some more details. OK, now let's say you have these 2 lists: D AK 1 Part # 1 Part # 3 2 Part # 3 Part # 4 3 Part # 7 Part # 1 4 Part # 4 As you can see, Part # 1, 3 & 4 are in both lists, Part # 7 is not. In a cell someplace, you could put: =IF(ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)),"Not In Both","In Both") and drag it down 3 more cells to get: =IF(ISNA(VLOOKUP(D2,$AK$1:$AK$3,1,0)),"Not In Both","In Both") etc. I'll break it down into its various parts so you'll see how it should work, based on my testing: =VLOOKUP(D1,$AK$1:$AK$3,1,0) will look up whatever is in D1 in AK1:AK3. If the value exists, it would return that value since we asked it to return the value in the first column of the lookup_array. The 0 tells Excel that AK1:AK3 does not need to be sorted. If VLOOKUP does not find the value in AK1:AK3, it will return #N/A. =ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)) will either return TRUE or FALSE depending on whether VLOOKUP returns a value or the #N/A error. We're basically asking Excel to tell us if the result of the VLOOKUP is #N/A or not: Is it #N/A? ISNA(). Get it? ;-) So, wrapping that in a IF statement we get: =IF(ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)),"Not In Both","In Both") If ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)) is FALSE, it means that VLOOKUP found the value in AK1:AK3 and therefore the value is in both columns. Since the value_if_false of the IF statement is "In Both" the formula will return "In Both" If ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)) is TRUE, it means that VLOOKUP did not find the value in AK1:AK3 and therefore the value is not in both columns. Since the value_if_true of the IF statement is "Not In Both" the formula will return "Not In Both" With the formula starting in E1,your final result should look like this: D E AK 1 Part # 1 In Both Part # 3 2 Part # 3 In Both Part # 4 3 Part # 7 Not In Both Part # 1 4 Part # 4 In Both Let me know if you need anything else.

0

@ cyoung311 re: What are you smoking? It's amazing how quickly you have become someone not worth having a conversation with.

0

That is an intelligent explanation of your previous assertion. I assumed the two columns were in a simple spreadsheet and that there was a one-to-one correspondence between the two columns. If you are assuming that there are two columns that could be in any order but still must be checked for similar contents then that changes the whole equation. Assumptions can be misleading...

0

@ cyoung311 Thank you for that response. I agree that assumptions can be misleading which is why you will see that in both of my responses to the OP I clearly stated what my assumptions were. However, regardless of what you were assuming, I don't think that there was any need for comments like "What are you smoking" and "Sheesh". We are all just trying to help each other and it would be a shame if this forum turned into one - like so many others - where insults are more prevalent than solutions.

0

Apologies. No insult intended. BTW, cool formula.

0

Cool...friends again!

0