I have a sheet with two columns (is more, but need the info about the two), say D (Part#) and AK (Part#).

I have to compare the two Col with part# to filter the usage out (usage is in Col AP and AS)

I just need to compare the two Col (one part# with the other) to see the same # is in both Columns. Any help out there?Thanks hr

Sort of like "if(<col1>=<col2>,"Same","Different")" would be if(D1=AK1,"Same","Different"). Then copy this formula to all of the rows in your table.

re: I just need to compare the two Col (one part# with the other) to see the same # is in both Columns.If that is all you really need to do, then run this formula down alongside column D:

=IF(ISNA(VLOOKUP(D1,AK:AK,1,0)),"Not In Both","In Both")

If the VOOKLUP doesn't find the part number in AK, it will return #N/A and the IF will return Not In Both. If it finds it, it will return "In Both"

@ cyoung311 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

One more note... If the list in AK is longer than the list in D, then swap the VLOOKUP arguments:

=IF(ISNA(VLOOKUP(AK1,D:D,1,0)),"Not In Both","In Both") and drag to the bottom of the AK list.

Hi and thank for the quick response.

I tried the: if(<col1>=<col2>,"Same","Different")" it don't work. It compare only one row with eachoteher. In my case:

one rowCol start with 13541 and the other row/Col with 15621. I would need some where it compares both col.

Hi i am back,

=IF(ISNA(VLOOKUP(AK1,D:D,1,0)),"Not In Both","In Both")

What does ISNA stay's for? I tried it but get only "In Both" back. That isn't true.

I sorted both columns with the part#, put the longer one now i column A, column B got the usage of last year, column c is on hand and colum F is the second column with part#.

I tried to to use the Vlookup my self but can't get it to work, so I tried your version. I also draged it to the bottom but it is ether (for all numbers the same) In Both or Not in Both.

@ 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!

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 # 4As 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_falseof 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_trueof 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 BothLet me know if you need anything else.

@ cyoung311 re:

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

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...

@ 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.

Apologies. No insult intended. BTW, cool formula.

Cool...friends again!

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History