Lookup in dependent tables.

[MichaelRobert]

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

 

[T. Valko]

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 A11
Table data in the range A28

Lookup references:

A16 = volts = 110
B16 = color = 2
C16 = flash

=INDEX(C28,MATCH(1,(A2:A8=A16)*(B2:B8=B16),0),MATCH(C16,C11,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!

 

[MichaelRobert]

Biff:

Many thanks. That works nicely.

Mike

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 A11
Table data in the range A28

Lookup references:

A16 = volts = 110
B16 = color = 2
C16 = flash

=INDEX(C28,MATCH(1,(A2:A8=A16)*(B2:B8=B16),0),MATCH(C16,C11,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!

 

[T. Valko]

You're welcome. Thanks for the feedback!