Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare & match text
In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both columns of course. Now I need to check that if John is Kenns room mate then Kenn is also Johns room mate. Could anyone please help me. Thanks, Susanne |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare & match text
On May 22, 2:29 pm, Susanne wrote:
In column A I have the names of the participants and in column B I have the names of their room mate. Meaning that everybody are mentioned in both columns of course. Now I need to check that if John is Kenns room mate then Kenn is also Johns room mate. Could anyone please help me. Thanks, Susanne In column C (C2) =IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match") You can then use filter on C or simply spot inconsistencies. HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare & match text
I have changed the formula according to my worksheet but it only gives me
N/A... I have my names/room mates in the columns of B and Q, and my Excel uses the ; instead of commas to separate in formulas. Could you please look through it for me? Have I done wrong ...? My first row is #3. =IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Rgs Susanne "vezerid" wrote: On May 22, 2:29 pm, Susanne wrote: In column A I have the names of the participants and in column B I have the names of their room mate. Meaning that everybody are mentioned in both columns of course. Now I need to check that if John is Kenns room mate then Kenn is also Johns room mate. Could anyone please help me. Thanks, Susanne In column C (C2) =IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match") You can then use filter on C or simply spot inconsistencies. HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare & match text
On May 22, 3:26 pm, Susanne wrote:
I have changed the formula according to my worksheet but it only gives me N/A... I have my names/room mates in the columns of B and Q, and my Excel uses the ; instead of commas to separate in formulas. Could you please look through it for me? Have I done wrong ...? My first row is #3. =IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Rgs Susanne "vezerid" wrote: On May 22, 2:29 pm, Susanne wrote: In column A I have the names of the participants and in column B I have the names of their room mate. Meaning that everybody are mentioned in both columns of course. Now I need to check that if John is Kenns room mate then Kenn is also Johns room mate. Could anyone please help me. Thanks, Susanne In column C (C2) =IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match") You can then use filter on C or simply spot inconsistencies. HTH Kostis Vezerides Hmmm, I know I had a small mistake in my formula, but I am not sure it should cause #N/A. Anyway, try the following, small modification of your formula: =IF(INDEX($Q$3:$Q$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Let us know if this one works. Kostis |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare & match text
Sorry, still gets the #N/A..
Susanne "vezerid" wrote: On May 22, 3:26 pm, Susanne wrote: I have changed the formula according to my worksheet but it only gives me N/A... I have my names/room mates in the columns of B and Q, and my Excel uses the ; instead of commas to separate in formulas. Could you please look through it for me? Have I done wrong ...? My first row is #3. =IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Rgs Susanne "vezerid" wrote: On May 22, 2:29 pm, Susanne wrote: In column A I have the names of the participants and in column B I have the names of their room mate. Meaning that everybody are mentioned in both columns of course. Now I need to check that if John is Kenns room mate then Kenn is also Johns room mate. Could anyone please help me. Thanks, Susanne In column C (C2) =IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match") You can then use filter on C or simply spot inconsistencies. HTH Kostis Vezerides Hmmm, I know I had a small mistake in my formula, but I am not sure it should cause #N/A. Anyway, try the following, small modification of your formula: =IF(INDEX($Q$3:$Q$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Let us know if this one works. Kostis |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compare & match text
On May 22, 5:35 pm, Susanne wrote:
Sorry, still gets the #N/A.. Susanne "vezerid" wrote: On May 22, 3:26 pm, Susanne wrote: I have changed the formula according to my worksheet but it only gives me N/A... I have my names/room mates in the columns of B and Q, and my Excel uses the ; instead of commas to separate in formulas. Could you please look through it for me? Have I done wrong ...? My first row is #3. =IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Rgs Susanne "vezerid" wrote: On May 22, 2:29 pm, Susanne wrote: In column A I have the names of the participants and in column B I have the names of their room mate. Meaning that everybody are mentioned in both columns of course. Now I need to check that if John is Kenns room mate then Kenn is also Johns room mate. Could anyone please help me. Thanks, Susanne In column C (C2) =IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match") You can then use filter on C or simply spot inconsistencies. HTH Kostis Vezerides Hmmm, I know I had a small mistake in my formula, but I am not sure it should cause #N/A. Anyway, try the following, small modification of your formula: =IF(INDEX($Q$3:$Q$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match") Let us know if this one works. Kostis Try something simpler (I should have thought of it to start with). In C2: =VLOOKUP(B2,A:B,2,0) This should find the match of the pair. It should be the same name as in A2. See what you get with this formula. Kostis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Spreadsheet Assistance (Match, Compare) | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Compare and Match Functions | Excel Discussion (Misc queries) | |||
Compare and match names and extract a cell content | Excel Worksheet Functions | |||
Want to compare two columns and then fill with data from the match | Excel Worksheet Functions |