Hi Excel experts,
I need some help in identifying common numbers that are in different columns and rows and need to show a match with a basic result in a new column that says ‘MATCH’.
No sure what function to use: vlookup, if, match, index etc and not an expert in these.
I’m using excel 2003 at work, so I need a function that works in this version and my excel is medium at most. I need a formula that I can type in the new columns to find a match basically when two people ring the same person. My data is in different columns and rows.
Here is an example: I have 5 columns: ref field blank. Attr refer to name. Could not not copy table properly.
A B C D E
Ref Attr C Number R Number Attr
9999 1234 BOB
TOM 5555 9999
CLAIRE 1111 0001
SANDRA 2222 0001
0002 3333 YODA
0002 4444 DARTH
In the above example 9999 (unknown) rings 1234 (BOB)
On another occasion 555 (TOM) rings 9999 (unknown)
9999 is the ‘common number’ communicated between two different people, however
9999 was calling 1234 and on another occasion was receiving from 5555.
My data set could be every long so I don’t want to type in names and numbers specifically (there are loads) but rather search the spreadsheet (not table) using columns and rows.
I want the new column to show the word ‘MATCH’ when 9999 was called by 2 number or greater and this result is shown on the new column. I can then filter the results if need be. 9999 is simply the example, the formula will need to cater any number in column C or D.
Any ideas or help with a formula that will work to do this when looking for any matches by 2 callers or more. If the formula can also show ‘MATCH’ when Claire or Sandra ring the same person (0001 - unknown) or when 0002 (unknown) calls Yoda (3333) and Darth (4444). Data could be anywhere in column C and D.
Thank you very much, every grateful.
I know someone will know!
Adnan
I need some help in identifying common numbers that are in different columns and rows and need to show a match with a basic result in a new column that says ‘MATCH’.
No sure what function to use: vlookup, if, match, index etc and not an expert in these.
I’m using excel 2003 at work, so I need a function that works in this version and my excel is medium at most. I need a formula that I can type in the new columns to find a match basically when two people ring the same person. My data is in different columns and rows.
Here is an example: I have 5 columns: ref field blank. Attr refer to name. Could not not copy table properly.
A B C D E
Ref Attr C Number R Number Attr
9999 1234 BOB
TOM 5555 9999
CLAIRE 1111 0001
SANDRA 2222 0001
0002 3333 YODA
0002 4444 DARTH
In the above example 9999 (unknown) rings 1234 (BOB)
On another occasion 555 (TOM) rings 9999 (unknown)
9999 is the ‘common number’ communicated between two different people, however
9999 was calling 1234 and on another occasion was receiving from 5555.
My data set could be every long so I don’t want to type in names and numbers specifically (there are loads) but rather search the spreadsheet (not table) using columns and rows.
I want the new column to show the word ‘MATCH’ when 9999 was called by 2 number or greater and this result is shown on the new column. I can then filter the results if need be. 9999 is simply the example, the formula will need to cater any number in column C or D.
Any ideas or help with a formula that will work to do this when looking for any matches by 2 callers or more. If the formula can also show ‘MATCH’ when Claire or Sandra ring the same person (0001 - unknown) or when 0002 (unknown) calls Yoda (3333) and Darth (4444). Data could be anywhere in column C and D.
Thank you very much, every grateful.
I know someone will know!
Adnan
Last edited:
