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.

--
Regards,

OssieMac


"Bexi" wrote:

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

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


On Mar 4, 1:58*pm, Bexi <bexi_re...@yahoo.com> wrote:
> 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

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

Pete

On Mar 5, 12:48*am, JP <jp2...@earthlink.net> wrote:
> 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(na*me_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
>
> On Mar 4, 1:58*pm, Bexi <bexi_re...@yahoo.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

LOL you're welcome


--JP

On Mar 4, 8:23*pm, Pete_UK <pashu...@auditel.net> wrote:
> I like that term "air code" - I'll have to remember that !! <bg>
>
> Pete
>
> On Mar 5, 12:48*am, JP <jp2...@earthlink.net> wrote:
>
>