On Thu, 20 Nov 2008 14:23:01 -0800, NHMcCormick07
wrote:

I have a survey sheet where users have 4 questions to answer Yes or No to.
Depending on the combination of Yes's and No's, I need to return a certain
cell value from another worksheet.
There are 5 possible combinations (1=No, No, No, No/ 2=Yes, No, No, No/3=
Yes, Yes, No, No /4=Yes, Yes, Yes, No /5=Yes, Yes, Yes, Yes). So, for
example, if a user responds "No, No, No, No" I need the cell containing the
formula to return a value that is in another worksheet. The other value is
the "solution" so to speak to their responses. If they answer No to all the
questions, then the answer to their problem is the text that is in a cell on
the corresponding worksheet.
I am a MOUS trainer and a student has asked me to help them out with this
and I am stumped!
Thanks!



You could do something like in a cell on Sheet1:

=VLOOKUP(SUMPRODUCT((LEFT($A$1:$A$4)="y")*{1;2;4;8 }),Sheet2!$A$1:$B$5,2,FALSE)

The SUMPRODUCT function should return a unique number depending on the
combination of Yes/No in A1:A4.

VLOOKUP will return an #NA error if the combination is not a valid one.

Your table on Sheet2 should be set up as:

0 If the 10 day caller does not reach the contact, a one day follow up is
set for the billing specialist.
1 Option for YNNN
3 Option for YYNN
7 If the customer has disputes, necessary adjustments will be made and
the revised invoices will be sent. Follow up in 7 business days.
15 Option for YYYY

Depending on what you want to do in the event of an error (and also if you are
running Excel 2007), you can wrap the above in either an IF or an IFERROR
function.

For example, in Excel 2007:

=IFERROR(VLOOKUP(SUMPRODUCT((LEFT($A$1:$A$4)="y")
*{1;2;4;8}),Sheet2!$A$1:$B$5,2,FALSE),"Invalid Answers")

--ron