Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

Is it possible to match a list of items from other list of items in MS Excel? If it's possible, which formula is best?

user-image
Question ajoutée par Sreenath Menon , Repair Officer - Aviation MRO , Falcon Aviation Services LLC
Date de publication: 2013/08/05
Utilisateur supprimé
par Utilisateur supprimé

Sure, it is possible.
I always use a2 step technique to compare list A with list Z: If you want to check if a value of the1st column (A) is present in the2nd: column (Z), you just add in column (B) next to it the formula: =vlookup(A1;Z:Z;1;FALSE) - and copy this formula down, so all values are verified..
You will get an error message if the value is not present in list Z.
Then just filter out the records with the error messages to see which values of.
A are not present in Z.
To do a2-way compare, you can add a check on the2nd dataset as well, and add in column (AA): =vlookup(Z1;A:A;1;FALSE) - and copy this formula down.
You will get an error message if the value is not present in list A.
Then just filter out the records with the error messages again to see which values of Z are not present in A.

Muhammad Awais Ali
par Muhammad Awais Ali , Performance Manager , Gladstone Wiz Ltd

I you just want to check if items mentioned in a list are also availble in second list or not then the best method is to use "match" or "vlookup" formula.

I would recommend match formula because for this purpose it can be used in place of both vlookup and hlookup, and it also returns the position of that item it the other list.

Faiyazuddin Mohammed
par Faiyazuddin Mohammed , Research Associate , Cognizant (UBS Service Centre India Pvt. Ltd)

Well there are numerous ways to answer this question.

You can use Vlookup to get the relevant data.

If the formating, sequence  and spelling in both the lists are same then simply then use =a1=b1

it gives true if the both the cells contain same data if not it returns false.

 

Mike Emerson Pasaron
par Mike Emerson Pasaron , Safety Officer , Arabian Petrochemical Co. (PETROKEMYA)

It is possible Sreenath.
Johan put an efficient and easy formula.
This is what i can share provided with the assumptions, suppose you have a list of customers in List A (A1:A10) and you want to return how much they purchased.
Customer Name (C1:C10) and Amount Purchased (D1:D10) are in List B.
If you want to return how much did the customer purchased, you may use this formula: =INDEX($C$1:$D$10,MATCH($A1,$A$1:$A$10,0),2) then drag it all the way down.
Simplified formula: =INDEX (Range of List B, MATCH(1st Customer up to10th, List A,0 [Exact Match]), Amount Purchased Column).
I have used Absolute and Relative References so that it will be easier to copy or drag the formula for efficiency.
Hope it helps! :)

More Questions Like This