| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup
I am trying to look up a value in one list to provide me with a value I will
then use to lookup data in a second list. Essentially, I want to join 2 lists/tables in Excel. Here's the example: DISPLAY DETAILS LIST Selected?.....HoldingKey.....AcctSecRefKey.....Pos itionKey Y..........HK1..........ASR1..........PK1 N..........HK2..........ASR2..........PK2 N..........HK3..........ASR3..........PK3 ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN 1. For those rows in DISPLAY DETAILS LIST where Selected = Y, return the AcctSecRefKey value from DISPLAY DETAILS LIST. (Example results: ASR1) 2. For that AcctSecRefKey value, return the SecID value from ASR TABLE. (Example results: ID2) I want to use this formula so that I can lookup the apprporiate SecID and SecIDType values from the ASR TABLE for those rows where the user selected the row. I can easily create a formula to accomplish step #1: IF(ISERROR(VLOOKUP("Y",DisplayDetails,3,FALSE)),"S elect a row",(VLOOKUP("Y",DisplayDetails,3,FALSE))). I can easily create a formula to accomplish step #2 WHERE the Lookup_value (i.e., AcctSecRefKey) is "hard-coded": IF(ISERROR(VLOOKUP("ASR1",ASR,6,FALSE)),"Data Not Found",(VLOOKUP("ASR1",ASR,6,FALSE))) What I am struggling with is how to combine these 2 steps into a single formula. Please advise of any ideas or assistance on how to accomplish this. Thanks in advance! -- Thanks, Jen |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup
It sounds like your problem is solvable by nesting the lookups, e.g.:
VLOOKUP( VLOOKUP("Y",DisplayDetails,3,FALSE) ,ASR,6,FALSE) "JBush" wrote: I am trying to look up a value in one list to provide me with a value I will then use to lookup data in a second list. Essentially, I want to join 2 lists/tables in Excel. Here's the example: DISPLAY DETAILS LIST Selected?.....HoldingKey.....AcctSecRefKey.....Pos itionKey Y..........HK1..........ASR1..........PK1 N..........HK2..........ASR2..........PK2 N..........HK3..........ASR3..........PK3 ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN 1. For those rows in DISPLAY DETAILS LIST where Selected = Y, return the AcctSecRefKey value from DISPLAY DETAILS LIST. (Example results: ASR1) 2. For that AcctSecRefKey value, return the SecID value from ASR TABLE. (Example results: ID2) I want to use this formula so that I can lookup the apprporiate SecID and SecIDType values from the ASR TABLE for those rows where the user selected the row. I can easily create a formula to accomplish step #1: IF(ISERROR(VLOOKUP("Y",DisplayDetails,3,FALSE)),"S elect a row",(VLOOKUP("Y",DisplayDetails,3,FALSE))). I can easily create a formula to accomplish step #2 WHERE the Lookup_value (i.e., AcctSecRefKey) is "hard-coded": IF(ISERROR(VLOOKUP("ASR1",ASR,6,FALSE)),"Data Not Found",(VLOOKUP("ASR1",ASR,6,FALSE))) What I am struggling with is how to combine these 2 steps into a single formula. Please advise of any ideas or assistance on how to accomplish this. Thanks in advance! -- Thanks, Jen |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Join 2 Lists - Lookup value in 1 list & use result in 2nd look
Thanks, Eddie!! That is exactly what I needed (so simple and obvious, that I
just overlooked it previously). -- Thanks, Jen "Eddie O" wrote: It sounds like your problem is solvable by nesting the lookups, e.g.: VLOOKUP( VLOOKUP("Y",DisplayDetails,3,FALSE) ,ASR,6,FALSE) "JBush" wrote: I am trying to look up a value in one list to provide me with a value I will then use to lookup data in a second list. Essentially, I want to join 2 lists/tables in Excel. Here's the example: DISPLAY DETAILS LIST Selected?.....HoldingKey.....AcctSecRefKey.....Pos itionKey Y..........HK1..........ASR1..........PK1 N..........HK2..........ASR2..........PK2 N..........HK3..........ASR3..........PK3 ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN 1. For those rows in DISPLAY DETAILS LIST where Selected = Y, return the AcctSecRefKey value from DISPLAY DETAILS LIST. (Example results: ASR1) 2. For that AcctSecRefKey value, return the SecID value from ASR TABLE. (Example results: ID2) I want to use this formula so that I can lookup the apprporiate SecID and SecIDType values from the ASR TABLE for those rows where the user selected the row. I can easily create a formula to accomplish step #1: IF(ISERROR(VLOOKUP("Y",DisplayDetails,3,FALSE)),"S elect a row",(VLOOKUP("Y",DisplayDetails,3,FALSE))). I can easily create a formula to accomplish step #2 WHERE the Lookup_value (i.e., AcctSecRefKey) is "hard-coded": IF(ISERROR(VLOOKUP("ASR1",ASR,6,FALSE)),"Data Not Found",(VLOOKUP("ASR1",ASR,6,FALSE))) What I am struggling with is how to combine these 2 steps into a single formula. Please advise of any ideas or assistance on how to accomplish this. Thanks in advance! -- Thanks, Jen |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Join 2 Lists - Lookup value in 1 list & use result in 2nd look
I have a follow-up question using my same sample data:
ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN I want to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2. Basically, the user will enter a SecIDKey value in a cell (A1), and then I want the formula to create a row listing of the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4). I've seen several posts that hint at how to do this, but haven't found one yet that explicitly defines how to accomplish this. Thanks in advance for assistance. -- Thanks, Jen "JBush" wrote: Thanks, Eddie!! That is exactly what I needed (so simple and obvious, that I just overlooked it previously). -- Thanks, Jen "Eddie O" wrote: It sounds like your problem is solvable by nesting the lookups, e.g.: VLOOKUP( VLOOKUP("Y",DisplayDetails,3,FALSE) ,ASR,6,FALSE) "JBush" wrote: I am trying to look up a value in one list to provide me with a value I will then use to lookup data in a second list. Essentially, I want to join 2 lists/tables in Excel. Here's the example: DISPLAY DETAILS LIST Selected?.....HoldingKey.....AcctSecRefKey.....Pos itionKey Y..........HK1..........ASR1..........PK1 N..........HK2..........ASR2..........PK2 N..........HK3..........ASR3..........PK3 ASR TABLE AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN 1. For those rows in DISPLAY DETAILS LIST where Selected = Y, return the AcctSecRefKey value from DISPLAY DETAILS LIST. (Example results: ASR1) 2. For that AcctSecRefKey value, return the SecID value from ASR TABLE. (Example results: ID2) I want to use this formula so that I can lookup the apprporiate SecID and SecIDType values from the ASR TABLE for those rows where the user selected the row. I can easily create a formula to accomplish step #1: IF(ISERROR(VLOOKUP("Y",DisplayDetails,3,FALSE)),"S elect a row",(VLOOKUP("Y",DisplayDetails,3,FALSE))). I can easily create a formula to accomplish step #2 WHERE the Lookup_value (i.e., AcctSecRefKey) is "hard-coded": IF(ISERROR(VLOOKUP("ASR1",ASR,6,FALSE)),"Data Not Found",(VLOOKUP("ASR1",ASR,6,FALSE))) What I am struggling with is how to combine these 2 steps into a single formula. Please advise of any ideas or assistance on how to accomplish this. Thanks in advance! -- Thanks, Jen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Table_array lookup through a define list | Excel Worksheet Functions | |||
| auto updating list | Excel Worksheet Functions | |||
| Currency to Text | Excel Worksheet Functions | |||
| VBA | Excel Worksheet Functions | |||
| I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Linear Mode
