Compare two worksheets where match is partial

[Bexi]

Hello,

I tried using the VLOOKUP AND MATCH functions, but they don't work for
the data I have.
I have two worksheets and they don't have unique identifiers. Below is
an illustration of my data.

Worksheet A
Credit Card Number Last First REF Amount
AXXXXXXXXXXX91009 SMITH MARY F08GXSA308 477.50

Worksheet B
Credit Card Number Name Amount
1234-123456-91009 SMITH/MARY 477.50

I want to match the credit card number, the person's name and amount.
I want to pull the REF data based on the condition stated.

Please help.
Thanks

 

[OssieMac]

Hi Bexi,

You need to insert another column and create unique identifiers from the
data you have. In the below example, I have inserted a column to the left of
the credit card number so that the new column is column A.

In Worksheet A:- Assume that the credit card number is in B2 and the Last
Name is in C2. Insert this formula in A2.

=RIGHT(B2,5)& " " &C2

You should get 91009 SMITH

In Worksheet B assume that the credit card number is in B2 and the Name is
in C2. Insert this formula in A2.

=RIGHT(B2,5) & " " &LEFT(C2,SEARCH("/",C2,1)-1)

You should get 91009 SMITH (Same result as above)

Of course it assumes that you have the slash "/" between last name and first
name in all cases on worksheet B.

Now that you have a unique identifier you should be able to perform the
matches required.

 

[JP]

In addition to what OssieMac suggested, how about an INDEX/MATCH
formula?

=INDEX(Ref_Col,MATCH(1,(("credit card
number"=CC_Col)*(LEFT(name_cell,FIND("/",name_cell)-1)=LName_Col)*(RIGHT(name_cell,LEN(name_cell)-
FIND("/",name_cell)))*("amount"=Amount_Col)),0))

Ref_Col is the range containing the ref numbers
"credit card number" is a cell reference to the cc number are trying
to match
CC_Col is the range containing the credit card numbers
name_cell is the cell containing the full name (with the slash in it)
LName_Col is the range of last names
"amount" is the cell reference to the amount you want to match
Amount_Col is the range containing the amounts


Enter as array formula (ctrl-shift-enter).

This was air code so please post back if it doesn't work.


HTH,
JP

 

[Pete_UK]

I like that term "air code" - I'll have to remember that !! <bg>

Pete

 

[JP]

LOL you're welcome


--JP