Lookup on multiple oolumns

search the forum for cascading dropdowns. one of good articles is eg. here  

what you need is to build a hierarchy that by selecting a value at certain level of decision will shorten list of possible option on lower level.

in you simple case it may look like:

- define a list of impacts (here you basically needs just two columns - ID, name of impact)

- define list of Likehoods - here you will have 3 columns: impact = lookup to impacts list, likehood name/identification, risk name/identification

- then you will place 3 controls on the form - impact dropdown = lookup control pointing to impacts list, likehood control pointing to likehoods list showing name of likehood and filtered by value of impact control and finally a calculated value control with lookup function that will query likehoods list as well and by value of likehood control it will get risk name

so at runtime, when you select a impact, likehood option will be limited just to those defined for selected impact. when you in turn select a likehood you will get limited list of related risks. I assume that in your case you have single possible risk for given combination of impact & likehood, hence lookup function should do the job. if you have several poossible risks for a given combination you may replace calculated control with one another dropdown and let the user select respective risk.

you have originally written...

I also have a form where the users select the impact and likelihood

but it shouldn't matter if result of your formula is in valid lookup format 'ID;#Display value', it still should be usable in filter of dependent control.

solution you have worked out might work but it's sensitive to text changes. so if you update impact text in impact list it will not be followed by your calculated column in other list(s) and you application might stop working.

I will have a go at your lookup format of 'ID;#Display value', but what does this format mean exactly, eg.  what does the # stand for?

lookup control's and lookup function's return value is always in form of 'ID;#Display value'

ID is ID of item from referenced list (your risk A might have ID 1 in list of risks)

Display value is an item field you want have displayed as a result of lookup (so in your case it's a field where you store those A-E risk labels)

;# is simply separator between the two.

so, it's not MY format

so, if I understand your setup, you have calculated control that based on some input gives you an impact as single letters A-E.

then you have some likelihood selector.

and for combination of those two you have defined some risk in the list.

so I think above suggested approach is still applicable

- let the impact calculate as you need

- place lookup control on the form that will point to your list and display likelihood column. set it up to be filtered by value of previous calcultion (this assumes impact column in the list is kind of text type, not lookup)

- then place another calculated control on the form. I believe its formula should be somthing like this

parseLookup(lookup(('TheList','ID',parseLookup(LikelihoodControl,false),'RiskColumn),true)‍

This has been a great conversation. If an answer is provided, please mark the appropriate post