Match

[lefty78312]

I know I'm missing something easy with this one. I have 2 lists of items. Right now each list is less than 50 items long but they'll both grow. I would like to have a formula that will display only the matching items from both columns in a 3rd column. So if the word 'baseball' appears in both columns, I would like it to start a 3rd column listing 'baseball' (and any other matching words).

Thank you for your assistance.

 

[grizz]

if your list is in column A and column B and your new list is C then put this in C1 and copy down

=IF(ISERROR(MATCH(A1,$B$1:$B$100,0)),"",A1)

 

[T. Valko]

I know I'm missing something easy with this one. I have 2 lists of items. Right now each list is less than 50 items long but they'll both grow. I would like to have a formula that will display only the matching items from both columns in a 3rd column. So if the word 'baseball' appears in both columns, I would like it to start a 3rd column listing 'baseball' (and any other matching words).

Thank you for your assistance.

Try this...

Book1
	A	B	C	D
1	List1	List2	Matches	4
2	1	7	_	10
3	10	8	_	77
4	17	9	_	80
5	22	10	_	84
6	30	14	_	_
7	55	15	_	_
8	61	18	_	_
9	62	23	_	_
10	66	25	_	_
11	69	29	_	_
12	77	46	_	_
13	80	64	_	_
14	82	77	_	_
15	83	80	_	_
16	84	81	_	_
17	89	84	_	_
18	90	86	_	_
19	95	98	_	_
20	97	99	_	_
Sheet1

Enter this formula in D1. This will return the count of items in both lists.

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,B2:B20,0))))

Enter this array formula** in D2 and copy down until you get blanks. This will list the common items.

=IF(ROWS(D$2:D2)>D$1,"",INDEX(A:A,SMALL(IF(ISNUMBER(MATCH(A$2:A$20,B$2:B$20,0)),ROW(B$2:B$20)),ROWS(D$2:D2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[lefty78312]

Thank you. This is perfect except the items in columns A & B are items, not numbers. Is there any way to get it to match words?

 

[T. Valko]

Thank you. This is perfect except the items in columns A & B are items, not numbers. Is there any way to get it to match words?

It'll work with text just as well. I used numbers in the example just because it's easier to generate random numbers for display purposes than it is to generate random text.

 

[lefty78312]

Thank you very much; it works perfectly. Two final picayune questions: Is there some way to copy the formula down and have it show blanks instead of #NUM! if there's no match? And what change is necessary to show matches in more than 20 rows?

 

[T. Valko]

Thank you very much; it works perfectly. Two final picayune questions: Is there some way to copy the formula down and have it show blanks instead of #NUM! if there's no match? And what change is necessary to show matches in more than 20 rows?

The count formula entered in D1 is used to prevent the #NUM! errors. If you don't want to have a separate cell to get this count then you can add it to the extraction formula but that will make the extraction formula that much longer and less efficient.

I'm not sure what you mean by question 2.

You just need to copy the formula to enough rows that covers the maximum number of matches that are possible. If the lists are 20 rows long and you know that you will never have more than 10 matches then you need to copy the formula to at least 10 rows. You're the only one that knows how many matches may be possible.

If the number of items in the lists expands over time then you can use dynamic named ranges.

http://contextures.com/xlNames01.html#Dynamic