| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 columns to 2 columns in different worksheets
Worksheet1
I have a list of surnames in column A and a list of first names in column B. Worksheet2 I have a list of surnames in column A and a list of first names in column B. How do I show if there is any matches in BOTH the surnames and first names in both worksheets i.e. it displays MATCH in a column. I have tried using VLOOKUP tables but have been unsuccessful. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match 2 columns to 2 columns in different worksheets
In summary this is what I need as below
WORKSHEET 1 A B Surname First name 1 SMITH JOHN 2 JONES JANE 3 ADAMS ALAN 4 PETERS JULIE WORKSHEET 2 A B C Surname First name Match 1 SMITH ALAN NO 2 JONES JANE YES 3 ADAMS JOHN NO 4 RICE JULIE NO Both the surname and first name must match across the worksheets. "Mally" wrote: Worksheet1 I have a list of surnames in column A and a list of first names in column B. Worksheet2 I have a list of surnames in column A and a list of first names in column B. How do I show if there is any matches in BOTH the surnames and first names in both worksheets i.e. it displays MATCH in a column. I have tried using VLOOKUP tables but have been unsuccessful. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match 2 columns to 2 columns in different worksheets
Also there could be different numbers of rows in the two worksheets.
e.g. 200 rows in worksheet 1 but 5000 in worksheet 2. "Mally" wrote: Worksheet1 I have a list of surnames in column A and a list of first names in column B. Worksheet2 I have a list of surnames in column A and a list of first names in column B. How do I show if there is any matches in BOTH the surnames and first names in both worksheets i.e. it displays MATCH in a column. I have tried using VLOOKUP tables but have been unsuccessful. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match 2 columns to 2 columns in different worksheets
Somewhere on row 1 :
names matches (array formula, validate with CTRL+Shift+Enter) : =IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$A$1:$A $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"") first names matches on row1 too and array formula too : =IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$B$1:$B $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"") Drag down as needed PS : I used A1:B15 ranges on both sheets. Adjust to your needs. You should choose the greatest range. PPS : There should be a simpler formula but I cannot figuree it out. -- Regards. Daniel "Mally" a écrit dans le message de news: ... Also there could be different numbers of rows in the two worksheets. e.g. 200 rows in worksheet 1 but 5000 in worksheet 2. "Mally" wrote: Worksheet1 I have a list of surnames in column A and a list of first names in column B. Worksheet2 I have a list of surnames in column A and a list of first names in column B. How do I show if there is any matches in BOTH the surnames and first names in both worksheets i.e. it displays MATCH in a column. I have tried using VLOOKUP tables but have been unsuccessful. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match 2 columns to 2 columns in different worksheets
=if(isnumber(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0)),"yes","no") (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. ==== An alternative is to combine the names in one cell (say in column C) in the second sheet with a unique string between the fields: =a1&"..."&b1 Then look for a match against that helper column: =if(isnumber(match(a1&"..."&b1,othersheet!c:c,0)), "yes","no") (not an array formula) Mally wrote: In summary this is what I need as below WORKSHEET 1 A B Surname First name 1 SMITH JOHN 2 JONES JANE 3 ADAMS ALAN 4 PETERS JULIE WORKSHEET 2 A B C Surname First name Match 1 SMITH ALAN NO 2 JONES JANE YES 3 ADAMS JOHN NO 4 RICE JULIE NO Both the surname and first name must match across the worksheets. "Mally" wrote: Worksheet1 I have a list of surnames in column A and a list of first names in column B. Worksheet2 I have a list of surnames in column A and a list of first names in column B. How do I show if there is any matches in BOTH the surnames and first names in both worksheets i.e. it displays MATCH in a column. I have tried using VLOOKUP tables but have been unsuccessful. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match 2 columns to 2 columns in different worksheets
Somewhere on row 1 :
names matches (array formula, validate with CTRL+Shift+Enter) : =IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$A$1:$A $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"") first names matches on row1 too and array formula too : =IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$B$1:$B $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"") Drag down as needed PS : I used A1:B15 ranges on both sheets. Adjust to your needs. You should choose the greatest range. PPS : There should be a simpler formula but I cannot figuree it out. PPPS : Please, ignore if this is the second post. I have not seen the first one. -- Regards. Daniel |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match 2 columns to 2 columns in different worksheets
Thank you all for your help. I'll try these as soon as I can
"Daniel.C" wrote: Somewhere on row 1 : names matches (array formula, validate with CTRL+Shift+Enter) : =IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$A$1:$A $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"") first names matches on row1 too and array formula too : =IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$B$1:$B $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"") Drag down as needed PS : I used A1:B15 ranges on both sheets. Adjust to your needs. You should choose the greatest range. PPS : There should be a simpler formula but I cannot figuree it out. PPPS : Please, ignore if this is the second post. I have not seen the first one. -- Regards. Daniel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to match 2 columns, if a match found add info from 2nd column | Excel Worksheet Functions | |||
| How to make columns in one sheet match columns from another sheet | Excel Discussion (Misc queries) | |||
| help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
| How do I copy columns between worksheets if the columns don't matc | Excel Worksheet Functions | |||
| How do i Match all COLUMNS? | Excel Worksheet Functions | |||
Linear Mode
