Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, IF, concantenate, Right -- can all these be in one formu
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, IF, concantenate, Right -- can all these be in one formu
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, IF, concantenate, Right -- can all these be in one f
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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, IF, concantenate, Right -- can all these be in one f
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 . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, IF, concantenate, Right -- can all these be in one formu
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup, IF, concantenate, Right -- can all these be in one f
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 ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reverse Concantenate? | Excel Worksheet Functions | |||
concantenate text to date | Excel Discussion (Misc queries) | |||
Concantenate Help | Excel Worksheet Functions | |||
concantenate formula? | Excel Discussion (Misc queries) | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |