View Full Version : Solved: Matching
majaro
05-20-2008, 12:45 PM
I have three columns. A,B and C. I want to match the entry in column A with entries in columns B and C. The entry in Column A could appear anywhere in B or C. If there is a match, then place the entry in column A next to the corresponding entry in columns B or C in column D. I know you have yo use a combo of INDEX and MATCH to make it work I think but cant figure it out.
Bob Phillips
05-20-2008, 04:00 PM
D2: =IF(NOT(ISNA(MATCH(B2,A:A,0))),B2,IF(NOT(ISNA(MATCH(C2,A:A,0))),C2,""))
and copy down
majaro
05-20-2008, 05:34 PM
Thanks Bob.
majaro
05-27-2008, 12:40 PM
OK, I need to modify this slightly. Instead of the value of N8 placed in the cell, I need the value of E:E. Is there a modification that can be made?
=IF(NOT(ISNA(MATCH(N8,E:E,0))),N8,"")
Bob Phillips
05-27-2008, 01:24 PM
Sorry mate, I am not sure what you mean.
RonMcK3
05-27-2008, 06:18 PM
OK, I need to modify this slightly. Instead of the value of N8 placed in the cell, I need the value of E:E. Is there a modification that can be made?
=IF(NOT(ISNA(MATCH(N8,E:E,0))),N8,"")
majaro,
Will this give you the desired result?
=If(Not(ISNA(MATCH(E8,N:N,0))),E8,"")
Cheers!
majaro
05-28-2008, 06:03 AM
D2: =IF(NOT(ISNA(MATCH(B2,A:A,0))),B2,IF(NOT(ISNA(MATCH(C2,A:A,0))),C2,""))
In the attached workbook, look at the highlighted items in green. Cell E9 differs from Cell N18. I need the value of E9 placed in say T18. Im looking for unmatched only. Sorry for the confusion.
Bob Phillips
05-28-2008, 06:22 AM
What is supposed to be compared against what in this workbook? Do you realise that the names are not consistent, K2 is not in column A.
majaro
05-28-2008, 06:25 AM
In the OP i was just using columns ABC as an example. What really needs to be compared is col E to cols N and O. Place entry in col E in col T as unmatched.
Bob Phillips
05-28-2008, 06:57 AM
=IF(AND(NOT(ISNA(MATCH(E9,N:N,0))),NOT(ISNA(MATCH(E9,O:O,0)))),E9,"")
Why are you comparing E9 in row 18?
majaro
05-28-2008, 07:25 AM
Well that was just an example as to what I was looking for. The value in column E could be found anywhere in the other two columns.
Bob Phillips
05-28-2008, 07:53 AM
So is my formula the solution?
majaro
05-28-2008, 08:03 AM
Bob,
Having trouble with it. I think what I need to do is match the only unique thing which appears to be the provider. Each provider should in theory have the same cost center throughout. So I dont know if you can do a formula to do this or not. Take column D, find the corresponding entry in column K, then take what is in column E and see if that entry is what is in column N or O. HTH
Looks like thats the only way to do it.
Bob Phillips
05-28-2008, 08:10 AM
I am sure I could do you a formula, but I really have no idea what you are trying to do now.
majaro
05-28-2008, 08:43 AM
Sorry about the bad posts. See if the attached makes it any clearer. Thanks
Bob Phillips
05-28-2008, 10:20 AM
Okay, another go
=IF(COUNTIF($F2:$G2,$B2)=1,"",$B2)
majaro
05-28-2008, 10:46 AM
ok i think i figured out what I need to do to make it work. Let's write a formula that matches column E to M and if the data matches then move cell F2 to S2. I can then weed through them and pull out the non matching ones.
so maybe
=if(e2=$m),F2,""
Probably not right, but hey I tried
Bob Phillips
05-28-2008, 11:23 AM
Where does M come into it? That is nothing like the file you just showed me which was F2 and G2.
majaro
05-28-2008, 11:29 AM
had to add a couple of columns to it (the spreadsheet that is). I'll post the new one with this.
Bob Phillips
05-28-2008, 12:18 PM
Sowhat are we comparing against what?
majaro
05-28-2008, 12:24 PM
Comparing column E to column M. E can be anywhere in M. when you find a match copy whats in column F to column S. That should do it.
Bob Phillips
05-28-2008, 01:37 PM
S2: IF(ISNA(MATCH(E2,M:M,0)),"",F2)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.