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

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

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

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