Compare two worksheets where match is partial

  • Thread starter Thread starter Bexi
  • Start date Start date
B

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
 
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.
 
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
 
Back
Top