Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create Bar code Sheet w/lookups, index/match
On Worksheet 2 I am pulling data from a data base to create the bar code
sheet from on Worksheet 1. Worksheet 2 Data Column A Column B Column C Property PM Approver Asst Approver row 3 AA AA_PM AA_Asst row 4 BB BB_PM BB_Asst row 5 CC CC_PM CC_Asst range=Property range=PM_Approver range=Asst_Approver Worksheet 1 (Bar Code Sheet) Cell B7 is selected from a range called "property" on WS2 (A3:A5). Cell A12 is selected from a hardcoded DDL of "PM" or "Asst." If value in A12 = "PM", I need to lookup up the PM_Approver (WS2colB) associated with the property in WS2colA and the same to hold true for Asst_Approver in WS2colC in A12 = "Asst". Any assistance would be appreciated. Thanks so much. KK |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create Bar code Sheet w/lookups, index/match
Try this:
=VLOOKUP(B7,Sheet2!A3:C5,IF(A12="PM",2,IF(A12="Ass t",3,1)),0) Alternatively, if you want to make use of your named ranges you can do it like this: =IF(ISNA(MATCH(B7,Property, 0)),"",INDEX(INDIRECT(A12&"_Approver"),MATCH(B7,Pr operty,0))) Hope this helps. Pete On Sep 29, 6:27*pm, KalliKay wrote: On Worksheet 2 I am pulling data from a data base to create the bar code sheet from on Worksheet 1. * Worksheet 2 Data * * * * * Column A * * * * * *Column B * * * * * *Column C * * * * * Property * * * * * * PM Approver * * * *Asst Approver row 3 * * * AA * * * * * * * * * * AA_PM * * * * * * * AA_Asst row 4 * * * BB * * * * * * * * * * BB_PM * * * * * * * *BB_Asst row 5 * * * CC * * * * * * * * * *CC_PM * * * * * * * *CC_Asst * range=Property * * *range=PM_Approver * * range=Asst_Approver Worksheet 1 (Bar Code Sheet) Cell B7 is selected from a range called "property" on WS2 (A3:A5). *Cell A12 is selected from a hardcoded DDL of "PM" or "Asst." * If value in A12 = "PM", I need to lookup up the PM_Approver (WS2colB) associated with the property in WS2colA and the same to hold true for Asst_Approver in WS2colC in A12 = "Asst". Any assistance would be appreciated. *Thanks so much. KK |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create Bar code Sheet w/lookups, index/match
Hi,
Your vlookup formula could look like this =VLOOKUP(D13,Sheet1!A9:C10,MATCH(D13,{"PM","Asst"} ,0)+1,FALSE) You can add more items to the Match function but keep them in order by column you want to return. -- Thanks, Shane Devenshire "KalliKay" wrote: On Worksheet 2 I am pulling data from a data base to create the bar code sheet from on Worksheet 1. Worksheet 2 Data Column A Column B Column C Property PM Approver Asst Approver row 3 AA AA_PM AA_Asst row 4 BB BB_PM BB_Asst row 5 CC CC_PM CC_Asst range=Property range=PM_Approver range=Asst_Approver Worksheet 1 (Bar Code Sheet) Cell B7 is selected from a range called "property" on WS2 (A3:A5). Cell A12 is selected from a hardcoded DDL of "PM" or "Asst." If value in A12 = "PM", I need to lookup up the PM_Approver (WS2colB) associated with the property in WS2colA and the same to hold true for Asst_Approver in WS2colC in A12 = "Asst". Any assistance would be appreciated. Thanks so much. KK |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create Bar code Sheet w/lookups, index/match
Thank you so much. I used the first statement and it worked perfectly.
"KalliKay" wrote: On Worksheet 2 I am pulling data from a data base to create the bar code sheet from on Worksheet 1. Worksheet 2 Data Column A Column B Column C Property PM Approver Asst Approver row 3 AA AA_PM AA_Asst row 4 BB BB_PM BB_Asst row 5 CC CC_PM CC_Asst range=Property range=PM_Approver range=Asst_Approver Worksheet 1 (Bar Code Sheet) Cell B7 is selected from a range called "property" on WS2 (A3:A5). Cell A12 is selected from a hardcoded DDL of "PM" or "Asst." If value in A12 = "PM", I need to lookup up the PM_Approver (WS2colB) associated with the property in WS2colA and the same to hold true for Asst_Approver in WS2colC in A12 = "Asst". Any assistance would be appreciated. Thanks so much. KK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting & Using Sheet Names or Index in VBA code | New Users to Excel | |||
Looking up data by row and column (Arrays, Lookups, Index, Match?? | Excel Worksheet Functions | |||
Help with lookups (Index/Offset/Match/Choose???) | Excel Discussion (Misc queries) | |||
How to use index match for multi lookups? | Excel Worksheet Functions | |||
INDEX / MATCH performance for lookups | Excel Worksheet Functions |