comparing a set of coloumns in excel ( VLOOKUP not working)

[theHermes5]

Hello everyone. I have been breaking my head with this problem since morning and I haven't found a solution. Please give your valuable pointers if possible, so that I can try to find the solution.

I basically have two sets of data- an old list and a new list. I wish to compare the new list( comparing name and country together) with an old list, since the new list has a few additional entries. Later on, I would like to create a new list with common entries from both old and new list and add all the new entries below the common ones ( if possible, else I will do that manually later on but I would like Excel to tell me that this is a new entry). Sorry, if this has not been well explained, but maybe the following screenshot helps.

As can be seen in this attachment, I have an old list with Item No., Name and Country. Let's assume that the item numbers have been classified based on some code words. In the second list, there are again Item No.s, Name and Country but some item numbers haven't been filled ( since they are new and have not yet been sorted). Now, I want Excel to compare the names AND countries of both data and provide the common Item No. output if there is a match. If there is no match, then I would like Excel to tell me that this is a new entry. I looked up on various forums and I realized that VLOOKUP command only allows me to search on Name OR Country would give me the common entries of Names/Countries respectively but not Item No.s. Please look at the desired output in the attachment.
Excel_Comparison.png

Please let me know if there are other formulas that I can use for such an output. Thanks in advance for your help.

[RudiS]

You can use VLOOKUP, but the trick is to concatenate the Name and Country first.
In both lists create an additional column and join Name and Country IE: Name&Country (no need for spaces.)
Then use VLOOKUP: =VLOOKUP(Name&Country,Table_Array,Col_Index_No,False)

BTW: I could provide better assistance if you attach a sample file to illustrate on. I don't want to type out data from a picture.

[Amik555]

Here is the solution, it's not perfect but does the job :-)

[theHermes5]

Here is the solution, it's not perfect but does the job :-)

Thank you Amik555 and RudiS for answering my question. This solution works fine for this sample data. Unfortunately, my original data has a lot more coloumns in between for eg. Item No., Name, Wholesale Price, Retail Price, Profit, Country etc and it is possible that the name and country might be similar between old data and new data but the wholesale price, retail price, profit maybe different. I assume that this approach might not work since we cannot sort the data properly anymore. Sorry, if the additional coloumsn should have been explained before.

[hemesh]

@hermes I would suggest you to please attach a mock of sample which clearly describes your problem and expected answers so best solution can be tailored.

[theHermes5]

Please see this attachment for the sample data. ( sorry, I did not know how to attach an Excel sheet, therefore, I took a screenshot).
Excel_Comparison_Revised.png

[hemesh]

assuming your old list is in A1 to F11 and New list is in J3 to N12
in B17 copy paste below

Please Login or Register  to view this content.

Then hold control shift and Enter together and then hit enter to make it array formula drag down until you get blank

Now copy paste the same formula in C17 and change Red Part to $K$3:$K$12 hold control and shift together and then hit enter to make it array formula drag down until you get blank.

Now copy paste the same formula in D17 and change Red Part to $N$3:$N$12 hold control and shift together and then hit enter to make it array formula drag down until you get blank

Change the references according to your actual data set.