Vlookup, IF, concantenate, Right -- can all these be in one formu

I am trying to do a vlookup from one tab with the lookup value a combination
of B and the last 4 digits of A. Comparing that value to the other tab that
also requires combining B and last 4 digits of B. If it does find it, I need
a blank cell returned. If it does not find it, I would like an "X" returned.

I have found many formulas that have bits and pieces. I am not successful in
nesting all of these things together. I have spent way too much time trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause problems in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"SRH@Boise" wrote in message
...
I am trying to do a vlookup from one tab with the lookup value a
combination
of B and the last 4 digits of A. Comparing that value to the other tab
that
also requires combining B and last 4 digits of B. If it does find it, I
need
a blank cell returned. If it does not find it, I would like an "X"
returned.

I have found many formulas that have bits and pieces. I am not successful
in
nesting all of these things together. I have spent way too much time
trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause problems in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH

Try this...

=IF(SUMPRODUCT(--(RIGHT(Transaction!C$2:C$13,4)&Transaction!B$2:B$1 3=RIGHT(A2,4)&B2)),"","X")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"SRH@Boise" wrote in message
...
I am trying to do a vlookup from one tab with the lookup value a
combination
of B and the last 4 digits of A. Comparing that value to the other tab
that
also requires combining B and last 4 digits of B. If it does find it, I
need
a blank cell returned. If it does not find it, I would like an "X"
returned.

I have found many formulas that have bits and pieces. I am not successful
in
nesting all of these things together. I have spent way too much time
trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause problems
in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH

I am on 2003
--
SRH


"T. Valko" wrote:

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"SRH@Boise" wrote in message
...
I am trying to do a vlookup from one tab with the lookup value a
combination
of B and the last 4 digits of A. Comparing that value to the other tab
that
also requires combining B and last 4 digits of B. If it does find it, I
need
a blank cell returned. If it does not find it, I would like an "X"
returned.

I have found many formulas that have bits and pieces. I am not successful
in
nesting all of these things together. I have spent way too much time
trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause problems in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH


.

I need it in Vlookup form. As is below with the absolute values is not
working for me either. Looks like the forumla is looking at the Transaction
tab for both parts of the formula, was that intentional. I need to look if
the Right 4 digits of one column plus the name in the second column
(together) on the Cardholder tab exists on the Transaction tab with the same
columns and formats added together in the criteria.


For example: I am looking to create this criteria from the Cardholder tab:
0064 RUSS H THACKERY
to be used in a vlookup to see if the same combination exists on the
Transaction Tab. The data starts out looking like this:

Cardholder Tab
First Column 2nd Column
556932******0064 RUSS H THACKERY

Transaction Tab
5569325*****0064 RUSS H THACKERY

If '0064 RUSS H THACKERY' exists on the Transaction tab, I would like a
blank on the Cardholder Tab. If it does not exist, an "X".



--
SRH


"T. Valko" wrote:

Try this...

=IF(SUMPRODUCT(--(RIGHT(Transaction!C$2:C$13,4)&Transaction!B$2:B$1 3=RIGHT(A2,4)&B2)),"","X")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"SRH@Boise" wrote in message
...
I am trying to do a vlookup from one tab with the lookup value a
combination
of B and the last 4 digits of A. Comparing that value to the other tab
that
also requires combining B and last 4 digits of B. If it does find it, I
need
a blank cell returned. If it does not find it, I would like an "X"
returned.

I have found many formulas that have bits and pieces. I am not successful
in
nesting all of these things together. I have spent way too much time
trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause problems
in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH




.

I am looking to start with this
=IF(ISNA(VLOOKUP(B1,'cardholder list'!$A$1:$C$50,3,FALSE)=TRUE),"x","")
and build in the Right 4 digits and concantenate in the logic.
--
SRH


"SRH@Boise" wrote:

I am trying to do a vlookup from one tab with the lookup value a combination
of B and the last 4 digits of A. Comparing that value to the other tab that
also requires combining B and last 4 digits of B. If it does find it, I need
a blank cell returned. If it does not find it, I would like an "X" returned.

I have found many formulas that have bits and pieces. I am not successful in
nesting all of these things together. I have spent way too much time trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause problems in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH

I need it in Vlookup form.

Why?

If I understood what you wanted then the formula I suggested does what you
wanted. Plus, it's better than using VLOOKUP.

--
Biff
Microsoft Excel MVP


"SRH@Boise" wrote in message
news
I need it in Vlookup form. As is below with the absolute values is not
working for me either. Looks like the forumla is looking at the
Transaction
tab for both parts of the formula, was that intentional. I need to look if
the Right 4 digits of one column plus the name in the second column
(together) on the Cardholder tab exists on the Transaction tab with the
same
columns and formats added together in the criteria.


For example: I am looking to create this criteria from the Cardholder tab:
0064 RUSS H THACKERY
to be used in a vlookup to see if the same combination exists on the
Transaction Tab. The data starts out looking like this:

Cardholder Tab
First Column 2nd Column
556932******0064 RUSS H THACKERY

Transaction Tab
5569325*****0064 RUSS H THACKERY

If '0064 RUSS H THACKERY' exists on the Transaction tab, I would like a
blank on the Cardholder Tab. If it does not exist, an "X".



--
SRH


"T. Valko" wrote:

Try this...

=IF(SUMPRODUCT(--(RIGHT(Transaction!C$2:C$13,4)&Transaction!B$2:B$1 3=RIGHT(A2,4)&B2)),"","X")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"SRH@Boise" wrote in message
...
I am trying to do a vlookup from one tab with the lookup value a
combination
of B and the last 4 digits of A. Comparing that value to the other
tab
that
also requires combining B and last 4 digits of B. If it does find it,
I
need
a blank cell returned. If it does not find it, I would like an "X"
returned.

I have found many formulas that have bits and pieces. I am not
successful
in
nesting all of these things together. I have spent way too much time
trying
on my own so I appreciate any help you may have.

I also found the * to cause me some problems, which also cause
problems
in
find and replace or trimming etc so I am trying the right command to
circumvent that issue.

Same data below:


Tab name:cardholder
a b
AccountNumber Name
556932******0064 RUSS H THACKERY
556932******0072 TODD S CHRISTIAN
556932******0080 TRACIE L MURRAY
556932******0098 BARBARA G GERDE
556932******0106 ERICA L FREY-HOYER

Tab name:Transaction

a b c
d
Unit Name Cardholder Name Account # Count
Company RUSS H THACKERY 556932******0064 1
Company TODD S CHRISTIAN 556932******0072 2
Company BARBARA G GERDE 556932******0098 1
Company ERICA L FREY-HOYER 556932******0106 14
Company WILLIAM G SCHULTZ 556932******0114 24
Company GLENDA D HAGERMAN 556932******0122 4
Company JOHN M LEVERMAN 556932******0148 8
Company KAY C BELL 556932******0155 4
Company RICHARD J WATTE 556932******0163 15
Company ANN R ANDERSON 556932******0171 11
Company CARROL J BYRD 556932******0189 8
Company CHERYL LAVOIE 556932******0197 3


--
SRH




.