flow chart vlookup if statements

[Guest]

I am trying to convert a flow chart into series of questions. Right now I
have a flow chart that asks a yes/no question. I would like to set up
something where it asks the first question and then if they answer yes, it
gives them the next question if no, then it asks a different questions. I can
do this for one question just using an IF statement but once I get to the
second question I am stuck since the second question will be different
depending on if the answer to the first was yes or no.
Any suggestions?

 

[Guest]

What does this have to do with VLOOKUP?

Dave

 

[Guest]

I thought that I might be able to list all of the questions in a table and
use VLOOKUP to pull the correct question. Not a good idea?

 

[vezerid]

Actually a VLOOKUP (or INDEX/MATCH) might be just the idea but a lot
depends on what you store where. The flow chart you are describing
seems like a tree, where the last question is the parent and correct/
incorrect are two paths stemming from the parent into the two children
questions. WHen storing graphs as tables (and trees in this case) the
following structure can work:

QuestionID ParentID Path Question
1 null null What is my name?
2 1 corr What is my last name?
3 1 incorr What is John's name?

The first question is the root hence has no parent or path. Questions
2 and 3 are the questions after 1 (whence their parentID) and you go
there for a correct or incorrect answer respectively. Thus, assuming
these data start from A2 (row 1 are headers), and if the last question
asked is in F2 and the answer (corr/incorr) is in G2, then you can
retrieve the next question with an *array* formula like:

=INDEX(D220,MATCH(1,(B2:B20=F2)*(C2:C20=G2),0))

(since it is an array formula you have to commit with Shift+Ctrl
+Enter)

This is by no means a full solution to your problem but it might be a
start.

HTH
Kostis Vezerides

 

[Guest]

Don't you think your INDEX/MATCH suggestion is more appropriate than VLOOKUP?
Seems it offers more flexibility.

Dave

 

[vezerid]

Hi Dave,

Yes, of course INDEX/MATCH is always more versatile... In this
particular case it seems to be necessary. Hard to go far with VLOOKUP
if you handle data like this.

Kostis

 

[Guest]

Sorry, I do not understand how the columns are set up in your example. It
looks like you only have four columns so wouldn't the questions be asked in
column D?

 

[vezerid]

The entire flowchart is stored in columns A. Questions' text is in
column D indeed. This is the underlying structure needed to store a
tree-like graph. But we assume that the questions appear elsewhere, in
other cells. To build the whole thing might require considerable
effort and depends how you want to set it up. In fact my suggested
formula is half-complete, it only shows the philosophy. A semi-
automatic scheme:

In another area we have:
F2 contains 1 (QuestionID for the first question)
G2 contains:
=VLOOKUP(F2,A,4,0) -- the question text
H2 contains the answer supplied by the user.
I2 contains your grade (corr/incorr).

Now, in Row 3, we want to produce the next question, according to the
answer grade of the last question.
In F3 (array formula):
=INDEX($A$2:$A$100,MATCH(1,($B$2:$B$100=F2)*($C$2:$C$100=I2),0))
G3 is the copy down of G2. This will produce the new question
H3 is supplied by the user
I3 contains new grade

As I said, this is semiautomatic, it can take considerable work until
it is presentable, but this is one way along which it can be done. I
have to go, so maybe someone else will jump in later if necessary.

Does this help?

 

[Guest]

Thanks for your help. I will work on it.

The entire flowchart is stored in columns A. Questions' text is in
column D indeed. This is the underlying structure needed to store a
tree-like graph. But we assume that the questions appear elsewhere, in
other cells. To build the whole thing might require considerable
effort and depends how you want to set it up. In fact my suggested
formula is half-complete, it only shows the philosophy. A semi-
automatic scheme:

In another area we have:
F2 contains 1 (QuestionID for the first question)
G2 contains:
=VLOOKUP(F2,A,4,0) -- the question text
H2 contains the answer supplied by the user.
I2 contains your grade (corr/incorr).

Now, in Row 3, we want to produce the next question, according to the
answer grade of the last question.
In F3 (array formula):
=INDEX($A$2:$A$100,MATCH(1,($B$2:$B$100=F2)*($C$2:$C$100=I2),0))
G3 is the copy down of G2. This will produce the new question
H3 is supplied by the user
I3 contains new grade

As I said, this is semiautomatic, it can take considerable work until
it is presentable, but this is one way along which it can be done. I
have to go, so maybe someone else will jump in later if necessary.

Does this help?