Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in dependent tables.
I want to identify a unique part number for a light unit given that there are
three variables associated with it. The variables are voltage, color, and steady/flashing. Each voltage has bulbs of different colors; each voltage and color has alternatives of steady or flashing. So I really need to 'nest' my lookups. However, I am not having any luck figuring out how to do it. The IF(AND ...) function looks likely, but I would have to make a very complicated formula. Seems I am making the atsk harder than I need to. In a way, I want to emulate the Online Autoparts application. When you have entered the Make (Ford Truck) the display changes and asks you for Year. Then it changes again and asks you for Body details. Finally it changes again and asks for Engine Size. Any suggestions? Many thanks. (Btw, I am using Excel 2003) Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in dependent tables.
This all depends on how you have your table(s) setup.
IMO it would be easier to do using a single database type table. Like this: V = volts C = color F = flash S = steady ...........V.....C.....F.....S ..........32.....1....p/n...p/n ..........32.....2....p/n...p/n ..........32.....3....p/n...p/n ........110.....1....p/n...p/n ........110.....2....p/n...p/n ........220.....1....p/n...p/n ........220.....2....p/n...p/n Then use an array formula** like this to retrun the part number: Column headers in A1:D1 Table data in the range A2:D8 Lookup references: A16 = volts = 110 B16 = color = 2 C16 = flash =INDEX(C2:D8,MATCH(1,(A2:A8=A16)*(B2:B8=B16),0),MA TCH(C16,C1:D1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Or, you could just use a filter! -- Biff Microsoft Excel MVP "MichaelRobert" wrote in message ... I want to identify a unique part number for a light unit given that there are three variables associated with it. The variables are voltage, color, and steady/flashing. Each voltage has bulbs of different colors; each voltage and color has alternatives of steady or flashing. So I really need to 'nest' my lookups. However, I am not having any luck figuring out how to do it. The IF(AND ...) function looks likely, but I would have to make a very complicated formula. Seems I am making the atsk harder than I need to. In a way, I want to emulate the Online Autoparts application. When you have entered the Make (Ford Truck) the display changes and asks you for Year. Then it changes again and asks you for Body details. Finally it changes again and asks for Engine Size. Any suggestions? Many thanks. (Btw, I am using Excel 2003) Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in dependent tables.
Biff:
Many thanks. That works nicely. Mike "T. Valko" wrote: This all depends on how you have your table(s) setup. IMO it would be easier to do using a single database type table. Like this: V = volts C = color F = flash S = steady ...........V.....C.....F.....S ..........32.....1....p/n...p/n ..........32.....2....p/n...p/n ..........32.....3....p/n...p/n ........110.....1....p/n...p/n ........110.....2....p/n...p/n ........220.....1....p/n...p/n ........220.....2....p/n...p/n Then use an array formula** like this to retrun the part number: Column headers in A1:D1 Table data in the range A2:D8 Lookup references: A16 = volts = 110 B16 = color = 2 C16 = flash =INDEX(C2:D8,MATCH(1,(A2:A8=A16)*(B2:B8=B16),0),MA TCH(C16,C1:D1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Or, you could just use a filter! -- Biff Microsoft Excel MVP "MichaelRobert" wrote in message ... I want to identify a unique part number for a light unit given that there are three variables associated with it. The variables are voltage, color, and steady/flashing. Each voltage has bulbs of different colors; each voltage and color has alternatives of steady or flashing. So I really need to 'nest' my lookups. However, I am not having any luck figuring out how to do it. The IF(AND ...) function looks likely, but I would have to make a very complicated formula. Seems I am making the atsk harder than I need to. In a way, I want to emulate the Online Autoparts application. When you have entered the Make (Ford Truck) the display changes and asks you for Year. Then it changes again and asks you for Body details. Finally it changes again and asks for Engine Size. Any suggestions? Many thanks. (Btw, I am using Excel 2003) Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in dependent tables.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "MichaelRobert" wrote in message ... Biff: Many thanks. That works nicely. Mike "T. Valko" wrote: This all depends on how you have your table(s) setup. IMO it would be easier to do using a single database type table. Like this: V = volts C = color F = flash S = steady ...........V.....C.....F.....S ..........32.....1....p/n...p/n ..........32.....2....p/n...p/n ..........32.....3....p/n...p/n ........110.....1....p/n...p/n ........110.....2....p/n...p/n ........220.....1....p/n...p/n ........220.....2....p/n...p/n Then use an array formula** like this to retrun the part number: Column headers in A1:D1 Table data in the range A2:D8 Lookup references: A16 = volts = 110 B16 = color = 2 C16 = flash =INDEX(C2:D8,MATCH(1,(A2:A8=A16)*(B2:B8=B16),0),MA TCH(C16,C1:D1,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Or, you could just use a filter! -- Biff Microsoft Excel MVP "MichaelRobert" wrote in message ... I want to identify a unique part number for a light unit given that there are three variables associated with it. The variables are voltage, color, and steady/flashing. Each voltage has bulbs of different colors; each voltage and color has alternatives of steady or flashing. So I really need to 'nest' my lookups. However, I am not having any luck figuring out how to do it. The IF(AND ...) function looks likely, but I would have to make a very complicated formula. Seems I am making the atsk harder than I need to. In a way, I want to emulate the Online Autoparts application. When you have entered the Make (Ford Truck) the display changes and asks you for Year. Then it changes again and asks you for Body details. Finally it changes again and asks for Engine Size. Any suggestions? Many thanks. (Btw, I am using Excel 2003) Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions | |||
Lookup tables | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions |