Lookup in dependent tables.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MichaelRobert - 08 May 2008 16:06 GMT

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 to this Message

T. Valko - 08 May 2008 20:28 GMT

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),MATCH(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!

Signature

Biff
Microsoft Excel MVP

>I want to identify a unique part number for a light unit given that there
>are
[quoted text clipped - 21 lines]
>
> Mike

Reply to this Message

MichaelRobert - 08 May 2008 21:33 GMT

Biff:

Many thanks. That works nicely.

Mike

> This all depends on how you have your table(s) setup.
>
[quoted text clipped - 57 lines]
> >
> > Mike

Reply to this Message

T. Valko - 08 May 2008 21:57 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Biff:
>
[quoted text clipped - 68 lines]
>> >
>> > Mike

Reply to this Message