I have a spreadsheet with 2 different drop-down lists.

dropdown#1 has 4 choices
dropdown #2 has 8 choices

Depending on the combination of the choice, there are recommendations that I
need to give to the client. The recommendations are already typed in other
rows of the spreadsheet.

Is there a way to incorporate a formula into a blank cell that will pick the
appropriate recommendation based on the choices from the drop down list? I
was thinking an IF function but I know you can only have 7 IF functions
nested in one. I am not very experienced with IF functions either.

That is correct there would only be one recommendation for each como of
choices. I think I see what you are suggesting now....I hope. This would
work.
How would this formula work in E1:
in E1: = VLOOKUP($A$1&$B$1,$C;$D,2,0)

Would I need to change the format or just leave it like this?

Thanks

"Toppers" wrote:

Just to clarify:

If I choose "Intern" and "Undergraduatedegree" is there only one
recommendation?

My solution was based on having (for example) "internundergraduate" as a
"key" i.e. combining the two selections, in column C with the corresponding
recommendation (ONE only) in column D.

Would this work?

Basically, how do you want to identify the combinations of choices?

"Frank" wrote:

Could you elaborate on what goes into what cell, especially column C, D,E.
Here is an example of data to work with:

Dropdown 1: intern, mid-level, suprevisor, office supervisor
Dropdown 2: work experience, some college, 2 year degree, undergraduate
degree,etc.

Where should I type the recommendations so that they fit into your formula?
C1=1A, what is considered 1A? D1="choice 1" is this where my recommendation
should go. in E1 do I use that exact formula or do I need to add more becasue
of more choices?

Thanks for your help.

"Toppers" wrote:

Try this "model" :

Using Data Validation, A1=Dropdown #1 (values 1 to 4)
B1=Dropdown #2 (values A to H)

In columns C &D :

C1=1A, D1="choice 1"
C2=1B, d2 = "choice 2"
etc

in E1: = VLOOKUP($A$1&$B$1,$C;$D,2,0)

Or to allow for errors

=IF(ISNA(VLOOKUP($A$1&$B$1,$C;$D,2,0)),"No
match",VLOOKUP($A$1&$B$1,$C;$D,2,0))

HTH

"Frank" wrote:

I have a spreadsheet with 2 different drop-down lists.

dropdown#1 has 4 choices
dropdown #2 has 8 choices

Depending on the combination of the choice, there are recommendations that I
need to give to the client. The recommendations are already typed in other
rows of the spreadsheet.

Is there a way to incorporate a formula into a blank cell that will pick the
appropriate recommendation based on the choices from the drop down list? I
was thinking an IF function but I know you can only have 7 IF functions
nested in one. I am not very experienced with IF functions either.

If the data is in the columns I proposed, then there is no need to change the
formula.

= VLOOKUP($A$1&$B$1,$C:$D,2,0)

This matches the A & B choices against the C ("key") data and returns the
corresponding recommendation from volumn D.


"Frank" wrote:

That is correct there would only be one recommendation for each como of
choices. I think I see what you are suggesting now....I hope. This would
work.
How would this formula work in E1:
in E1: = VLOOKUP($A$1&$B$1,$C;$D,2,0)

Would I need to change the format or just leave it like this?

Thanks

"Toppers" wrote:

Just to clarify:

If I choose "Intern" and "Undergraduatedegree" is there only one
recommendation?

My solution was based on having (for example) "internundergraduate" as a
"key" i.e. combining the two selections, in column C with the corresponding
recommendation (ONE only) in column D.

Would this work?

Basically, how do you want to identify the combinations of choices?

"Frank" wrote:

Could you elaborate on what goes into what cell, especially column C, D,E.
Here is an example of data to work with:

Dropdown 1: intern, mid-level, suprevisor, office supervisor
Dropdown 2: work experience, some college, 2 year degree, undergraduate
degree,etc.

Where should I type the recommendations so that they fit into your formula?
C1=1A, what is considered 1A? D1="choice 1" is this where my recommendation
should go. in E1 do I use that exact formula or do I need to add more becasue
of more choices?

Thanks for your help.

"Toppers" wrote:

Try this "model" :

Using Data Validation, A1=Dropdown #1 (values 1 to 4)
B1=Dropdown #2 (values A to H)

In columns C &D :

C1=1A, D1="choice 1"
C2=1B, d2 = "choice 2"
etc

in E1: = VLOOKUP($A$1&$B$1,$C;$D,2,0)

Or to allow for errors

=IF(ISNA(VLOOKUP($A$1&$B$1,$C;$D,2,0)),"No
match",VLOOKUP($A$1&$B$1,$C;$D,2,0))

HTH

"Frank" wrote:

I have a spreadsheet with 2 different drop-down lists.

dropdown#1 has 4 choices
dropdown #2 has 8 choices

Depending on the combination of the choice, there are recommendations that I
need to give to the client. The recommendations are already typed in other
rows of the spreadsheet.

Is there a way to incorporate a formula into a blank cell that will pick the
appropriate recommendation based on the choices from the drop down list? I
was thinking an IF function but I know you can only have 7 IF functions
nested in one. I am not very experienced with IF functions either.

Thank you, I tested the formula and it works great!

"Toppers" wrote:

If the data is in the columns I proposed, then there is no need to change the
formula.

= VLOOKUP($A$1&$B$1,$C:$D,2,0)

This matches the A & B choices against the C ("key") data and returns the
corresponding recommendation from volumn D.


"Frank" wrote:

That is correct there would only be one recommendation for each como of
choices. I think I see what you are suggesting now....I hope. This would
work.
How would this formula work in E1:
in E1: = VLOOKUP($A$1&$B$1,$C;$D,2,0)

Would I need to change the format or just leave it like this?

Thanks

"Toppers" wrote:

Just to clarify:

If I choose "Intern" and "Undergraduatedegree" is there only one
recommendation?

My solution was based on having (for example) "internundergraduate" as a
"key" i.e. combining the two selections, in column C with the corresponding
recommendation (ONE only) in column D.

Would this work?

Basically, how do you want to identify the combinations of choices?

"Frank" wrote:

Could you elaborate on what goes into what cell, especially column C, D,E.
Here is an example of data to work with:

Dropdown 1: intern, mid-level, suprevisor, office supervisor
Dropdown 2: work experience, some college, 2 year degree, undergraduate
degree,etc.

Where should I type the recommendations so that they fit into your formula?
C1=1A, what is considered 1A? D1="choice 1" is this where my recommendation
should go. in E1 do I use that exact formula or do I need to add more becasue
of more choices?

Thanks for your help.

"Toppers" wrote:

Try this "model" :

Using Data Validation, A1=Dropdown #1 (values 1 to 4)
B1=Dropdown #2 (values A to H)

In columns C &D :

C1=1A, D1="choice 1"
C2=1B, d2 = "choice 2"
etc

in E1: = VLOOKUP($A$1&$B$1,$C;$D,2,0)

Or to allow for errors

=IF(ISNA(VLOOKUP($A$1&$B$1,$C;$D,2,0)),"No
match",VLOOKUP($A$1&$B$1,$C;$D,2,0))

HTH

"Frank" wrote:

I have a spreadsheet with 2 different drop-down lists.

dropdown#1 has 4 choices
dropdown #2 has 8 choices

Depending on the combination of the choice, there are recommendations that I
need to give to the client. The recommendations are already typed in other
rows of the spreadsheet.

Is there a way to incorporate a formula into a blank cell that will pick the
appropriate recommendation based on the choices from the drop down list? I
was thinking an IF function but I know you can only have 7 IF functions
nested in one. I am not very experienced with IF functions either.