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