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

S

SRH@Boise

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
 
T

T. Valko

Try this...

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

SRH@Boise

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".
 
S

SRH@Boise

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

T. Valko

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