THAT WAS GREAT!!!!

Max u didn't only solve Mike's but one of my very own problem as well.
However, I have a little different one i.e. if the two columns do not match
can I have a function to lookup in corresponding column D or E and return a
value randomly from any instead of returning an #N/A?

By the way sure am anxious to know why did you place "1" as lookup value in
Match function?

Thanx again buddy!

"Max" wrote:

One approach ..

Assume your 100 x 100 x 100 response/answer table is in a sheet: X,
within A1:C100 as below, where col C = answers

dog chair yes
dog table no
dog food maybe
cat chair no
cat table maybe
cat food yes
etc

Then in another sheet, where you have the corresponding DVs running in cols
A and B, from row1 down, you could place this array formula in C1, and
confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing
ENTER):

=IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$100,MATCH(1,( X!A$1:A$100=A1)*(X!B$1:B$100=B1),0)))

Copy C1 down as far as required. Col C will return the answers for the DV
selections made in cols A and B.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote:
-- I have a question involving exceeding the amount of IF statements.
Cell A1 has drop down list of 100 choices, the user selects one
Cell B1 has another drop down list of 100 choices, of which the user selects
one
Cell C1, once a selection is made in both A1and B1, give an answer from 4
possible values in C1.
i.e if a1 = dog and b1 = chair then c1 = yes

I know that I can only use 7 nested IF statement in a cell.
What would be the best way to display the values.
Insert criteria in cell, use a VBA macro?
If so what would the cell or VBA look like?
With a 100 possible selections in both a1 or b1, the simplest way would be
the best.

thanks to anyone who can help.
Mike