Solved: Matching

[majaro]

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]

D2: =IF(NOT(ISNA(MATCH(B2,A:A,0))),B2,IF(NOT(ISNA(MATCH(C2,A:A,0))),C2,""))

and copy down

[majaro]

Thanks Bob.

[majaro]

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]

Sorry mate, I am not sure what you mean.

[RonMcK3]

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]

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]

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]

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]

=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]

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]

So is my formula the solution?

[majaro]

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]

I am sure I could do you a formula, but I really have no idea what you are trying to do now.

[majaro]

Sorry about the bad posts. See if the attached makes it any clearer. Thanks

[Bob Phillips]

Okay, another go

=IF(COUNTIF($F2:$G2,$B2)=1,"",$B2)

[majaro]

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]

Where does M come into it? That is nothing like the file you just showed me which was F2 and G2.

[majaro]

had to add a couple of columns to it (the spreadsheet that is). I'll post the new one with this.

[Bob Phillips]

Sowhat are we comparing against what?

[majaro]

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]

S2: IF(ISNA(MATCH(E2,M:M,0)),"",F2)