Live Chat Software by Kayako |
Knowledgebase: Microsoft Excel
|
Retrieving Values that are Common to Two Lists
Posted by - NA - on 20 March 2006 08:45 AM
|
|
|
Problem: We want to create a new list consisting of the values that are common to both List1 (column A) and List2 (Column B). Solution: Use the INDEX, SMALL, IF, COUNTIF, and ROW functions as shown in the following Array formula: {=INDEX($A$2:$A$9,SMALL(IF(COUNTIF($B$2:$B$9,$A$2:$A$9)>0,ROW($A$2:$A$9),1000),ROW()-ROW($D$2)+1)-ROW($D$2)+1)} Enter the formula in cell D2 and copy it down the column until the #REF! error is returned. | |
 (353 vote(s)) This article was helpful This article was not helpful |
|
Comments (0)