Find results based on drop-down data

[Peter Quill]

Hello everyone,

I want to be able to choose from three dropdown lists, and based on the selected options from each list, generate a corresponding result in another cell. While I've experimented with IFS and ORS formulas, they only seem to work for the first result option and not the other two possibilities.

To clarify, the potential results are categorized as Low Risk, Medium Risk, and High Risk, with the dropdown lists corresponding to requested funding, motivation option, and education level in the respective columns.

Do you have any recommendations on enabling individuals to make selections from each dropdown to reveal a single result? The dropdown menus are located in the second row. Thanks!

 

[Gasman]

Would likely need AND() in there as well I would have thought?
I would probably create a dedicated function, as it would easier than trying to create a formula?

 

[cheekybuddha]

What result are you trying to reveal?

The corresponding Risk Level for a certain combination of choices?

Not all level combinations are covered - what happens when the three choices don't match a row in Sheet2!B4:E27 ?

 

[arnelgp]

just guessing, why not use a UserForm.

 

[cheekybuddha]

Add the following formula to a blank cell on Sheet2:

=IFERROR(INDIRECT("A"&SUMPRODUCT(--($B4:$B27=$B$2),--($C4:$C27=$C$2), --($D4:$D27=$D$2), ROW($A4:$A27))), "No Match")

Then, make selections in your second row dropdowns and see if the right risk level is displayed in the cell with the formula.

 

[Gasman]

Add the following formula to a blank cell on Sheet2:

=IFERROR(INDIRECT("A"&SUMPRODUCT(--($B4:$B27=$B$2),--($C4:$C27=$C$2), --($D4:$D27=$D$2), ROW($A4:$A27))), "No Match")

Then, make selections in your second row dropdowns and see if the right risk level is displayed in the cell with the formula.

Very neat.

 

[cheekybuddha]

Thanks, gm!

Gotta love that double unary operator!

 

[Gasman]

Thanks, gm!

Gotta love that double unary operator!

Never seen it before, if you are talking about the --?

 

[cheekybuddha]

Yup, extremely powerful in conjunction with SUMPRODUCT(), especially for those of us with less modern versions of Excel.

Newer versions of Excel have functions like (XLOOKUP() and other friends)

See here

 

[Gasman]

To save me looking it up, and for the benefit of others , exactly what does it do?

 

[cheekybuddha]

Did you see the link in my previous reply?

 

[Gasman]

Did you see the link in my previous reply?

No sorry, completely missed that.

 

[cheekybuddha]

No sorry, completely missed that.

Probably not your fault! I edited it in about two minutes after I posted

 

[Gasman]

Not to worry, I got my answer from that link, plus it is available to anyone else who might like the logic.

 

[cheekybuddha]

It basically acts like a boolean wrapper, returning 1 or 0 depending on the test within, and since you are working with arrays (ranges) then it evaluates for each value in the array.

Then Sumproduct multiplies all the values together; where not all the tests passed (ie a 0 was returned) then the product will be 0, but if all tests passed(ie all returned 1) then you get 1 * 1 * 1 * etc * your wished for value (ROW() in this case).

Although confusing at first, you might want to use the 'Evaluate Formula' button (in the Formulas tab I think) to step through each stage of the evaluation of the formula.

 

[cheekybuddha]

I tidied up the spreadsheet a bit:

1. Turned the main list in to a table
2. Created dynamic lists for the dropdowns based on the data in the table using this excellent tutorial
3. Created appropriate named ranges so the dropdown validation lists can be based off lists on a different sheet.
4. Adjusted the above formula to use table references instead of hardcoded ranges.

You *should* be able to add new entries to the table (MainData) and everything updates automatically.

Only things I didn't pursue further:
Handling of blank values
Automatically extending conditional format on addition of new row to table

 

[Gasman]

Always disappointing when you never hear back from the O/P and multiple posts are being made in the meantime

 

[cheekybuddha]

This is where we find out they were after something else entirely!

The OP did post in the wee hours of our morning so perhaps different timezones?

In any case, I learnt some things today: how to extract unique values from lists and how to use an excel table structured reference in my SUMPRODUCT formulae.

 

[cheekybuddha]

@Gasman, I managed to dig out the origins of it all (for me anyway!) on UA from the dearly missed NateO