Match Function

[Cheimbig]

In excel, if you have Column A that has a list of names – in Column B you
have another list of names and in Column C you want to show any from A and B
that match – do you know the formula to do this?

I found one that shows me the number of matches, but I actually want to see
the actual names that match.

Suggestions? Thanks in advance

 

[Pete_UK]

Put this in C1:

=IF(ISNA(MATCH(B1,A:A,0)),"",B1)

and copy down for as many names as you have in column B.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

Try the following steps:

1. Select the first range of name and convert it to a List (Ctrl+L).
2. Now assign a name to the first range of names, say Name1.
3. Follow steps 1 and 2 for the second range of names as well. Just assign
the second range a different name, say Name2
4. Save the file and save it on the desktop
5. Now click on any blank cell and go to:
a. Excel 2007 - Data > From Other Sources > Microsoft Query
b. Ecel 2003 - Data > Import External data > New Database query
6. In the Choose Data Source box, select Excel files. Click on OK
7. Select the Excel file (which you just saved on the desktop) and click on
OK
8. In the query wizard - choose columns, select Name1 and click on the
right arrow. Do the same for Name2. Click on Next
9. You will get message saying that the query wizard cannot continue.
Click on OK.
10. You will see 2 columns of names
11. Now go to Table > Joins and just click on Add and Close
12. Now remove the second column by clicking on any one entry and then go
to Records > Remove column. You will now see all the common entries.
13. File > Return Data to Microsoft Office Excel
14. In the box that comes up, select Properties and check the box for
Refresh every and select 1 minute. Also, check the box for Refresh when
opening the file. Click on OK, click on OK

You will not see all the common entries.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

Excel 2007 Tables
With Advanced Filter
Dynamic ranges, no copy/paste.
Number of cells with formulas: One
No code, no database query
http://www.mediafire.com/file/dknkmdyo2ny/08_08_09.xlsx

 

[Minister]

Assuming a similar table is what you're using:

Rows Col A Col B

1 Mike Mary
2 Larry Larry
3 Pete John
4 John Stephen

You can use the following formula to show where the same name appears in
both columns, type this in cell C1 and copy-paste onto the cells below it
(C2, C3....)
=IF(A1=B2,A1,"")