I have a sheet with 3 columns.  Each of these columns has a drop down
box.  The first box has 3 options (the numbers 20, 30, & 40), the
second box has 2 options (the numbers 9 & 13) and the third box has 2
options (the numbers 2 & 3).  I would like the 4 column to display
data based on what the first 3 choices are.  This data is from another
sheet within the same workbook.  For example, I need a statement that
says if A1=20 and B1=9 and C1=2 then display cell Z15 from the sheet
with the data.  I will also need this function to have the logic for
all 12 possible results, as they lead to 12 different cells in the
next sheet.  I know absolutely nothing about conditional statements
(especially those that are, at least for me, this complicated) in
Excel, so I would appreciate it if someone could write this function
for me.  If it makes any difference, the name of the sheet with the
data on it that I wish to reference is One Group.

---

Clarification of Question by tnsdan-ga on 24 Oct 2006 16:27 PDT

Thank you toufaroo, that was perfect.  Even the GA commentors are great:)

This is going to be a tad tricky, because Excel only allows you to use
8 IF statements in any one field.  That is to say, it only lets you
differentiate between 8  choices, not 12.  So, to work around this,
we'll create three "hidden" boxes, each addressing four choices. 
We'll then create a fourth box that addresses the final result!

For each of the first three "hidden boxes", we have to assume that A =
20, 30, and 40 respectively.

So, for hidden box number one (A=20), your formula will look something like:

=IF(AND(B1=9,C1=2),'One Group'!Z1,IF(AND(B1=9,C1=3),'One
Group'!Z2,IF(AND(B1=13,C1=2),'One Group'!Z3,IF(AND(B1=13,C1=3),'One
Group'!Z4))))

Place the above formula in cell G1 of your main worksheet.

Where Z1, Z2, Z3, and Z4 are in sheet 'One Group' with the appropriate
data.  You'll have to adjust these values as necessary for your
application.

Hidden Box number two (recall that this is for A=30):

=IF(AND(B1=9,C1=2),'One Group'!Z5,IF(AND(B1=9,C1=3),'One
Group'!Z6,IF(AND(B1=13,C1=2),'One Group'!Z7,IF(AND(B1=13,C1=3),'One
Group'!Z8))))

Place the above formula in  cell G2 of your main worksheet.

Hidden Box number three (A=40):

=IF(AND(B1=9,C1=2),'One Group'!Z9,IF(AND(B1=9,C1=3),'One
Group'!Z10,IF(AND(B1=13,C1=2),'One Group'!Z11,IF(AND(B1=13,C1=3),'One
Group'!Z12))))

Place the above formula in cell G3 of your main worksheet.

Your final box:

=IF(A1=20, G1, IF(A1=30, G2, IF(A1=40, G3)))

Place the above formula in cell D1 of your main worksheet.

For simplicity, I recommend that you make the "hidden boxes" white
text or otherwise make then invisible so that you don;t get confused
while using the spreadsheet.

Enjoy!

-toufaroo

To me, it seems the easiest way would be to use the Text function and
a lookup table. Assume that the drop-down boxes refer to A1, B1, and
C1. In cell D1 you would put the following formula:
=TEXT(A1,"000")&TEXT(B1,"000")&TEXT(C1,"000"). Then, in cell E1, you
would enter the formula =VLOOKUP(D1,$M$1:$N$12,2) where $M$1:$N$12
refers to a range of cells containing the possible combinations in the
"M" column and the associated values in the "N" column. In this
example I've just used the letters a,b,c, etc., but you could put any
value in.


M                     N
020009002	a
020009003	b
020013002	c
020013003	d
030009002	e
030009003	f
030013002	g
030013003	h
040009002	I
040009003	j
040013002	k
040013003	l

I'm not sure if this makes any sense, but thought it might work. I 
swear it's much simpler than I'm making it sound.

You're welcome - I'm glad it worked out for you!

-toufaroo

You might want to try this instead, it avoids the multiple columns:

=choose((A1/10-1)*4+(B1-9)/2+C1-1;$V$1;$V$2;$V$2;$V$3;$V$4;$V$5;$V$6;$V$7;$V$8;$V$9;$V$10;V$11;V$12)

The formula (A1/10-1)*4+(B1-9)/2+C1-1 will convert your 3 values into
a value from 1 through 12 and the choose function lets you select one
of the 12 values based on that index.
The dollar signs in $V$1 makes these adresses absolute so you can copy
the function and it will keep the same values.

Hope that helps.

- Alain