Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
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 -- Carla Heimbigner |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
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 On Aug 8, 12:14*am, Cheimbig wrote: 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 -- Carla Heimbigner |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
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 "Cheimbig" wrote in message ... 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 -- Carla Heimbigner |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
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,"") "Cheimbig" wrote: 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 -- Carla Heimbigner |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the second match using the match function | Excel Worksheet Functions | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |