PDA

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.

xld
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,"")

xld
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.

xld
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.

xld
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.

xld
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.

xld
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

xld
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

xld
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.

xld
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.

xld
05-28-2008, 01:37 PM
S2: IF(ISNA(MATCH(E2,M:M,0)),"",F2)