Google Answers Logo
View Question
Q: Excel Question ( No Answer,   4 Comments )
Subject: Excel Question
Category: Computers
Asked by: tnsdan-ga
List Price: $10.00
Posted: 24 Oct 2006 13:31 PDT
Expires: 23 Nov 2006 12:31 PST
Question ID: 776480
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:)
There is no answer at this time.

Subject: Re: Excel Question
From: toufaroo-ga on 24 Oct 2006 14:09 PDT
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

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

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

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

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.


Subject: Re: Excel Question
From: joet31124-ga on 24 Oct 2006 16:53 PDT
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.
Subject: Re: Excel Question
From: toufaroo-ga on 24 Oct 2006 19:19 PDT
You're welcome - I'm glad it worked out for you!

Subject: Re: Excel Question
From: agbinfo-ga on 03 Nov 2006 16:56 PST
You might want to try this instead, it avoids the multiple columns:


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

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy