Building a lookup table with 3 variables/3 answers

Hi I'm a new user of excel 2007 with basic skills.
I'm trying to figure out how best to build a table to do:

IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"

So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE"

I've done this with 2 variables, but can't seem to get it to work with three...

Thanks and looking forward to any assistance & advice.

The Mods will probably be after you for violating the forum rules re. your post title. So plan on changing it to something that more accurately identifies the problem.

In the meantime, your logic appears to be beyond understanding here. You have three questions, each with three possible answers, this gives you 27 possible answer combinations. How do you resolve this into just three (AT,IC,VE) results? Please be more descriptive in what you're trying to do.

Great!

So if you're willing to structure your lookup table (on a sheet named "Answers") a bit like this:

Col A ; Col B ; Col C ; Col D ; Col E
Ans 1 ; Ans 2 ; Ans 3 ; Concat ; Result
yes ; yes ; yes ; yesyesyes ; AT
yes ; yes ; no ; yesyesno ; AT
yes ; no ; na ; yesnona ; IC
etc.

In Cell D2 (and copied down) you can use the formula =CONCATENATE(A2,B2,C2)

You may need to sort your lookup table by column D to allow efficient use of the VLOOKUP formula.


Once the lookup table is built, just use columns D and E as the source for your VLOOKUP formula like this:

Please Login or Register  to view this content.

HTH