Sharepoint lists - lookup another list based on two column values

Question

Hi,
New to Sharepoint lists, previously did everything in excel. I have two lists, previously used index and match to get my result. I know I can use a very long if statement to get the result but was hoping instead to lookup anther list like I do in excel.

First List called 'Risk Register' I need to use two field values on the first list 'Inherent Consequence' and 'Inherent Likelihood', both choice fields, to lookup the second list 'DTIS Risk Matrix' to return the inherent Risk Level.

FIRST LIST - Risk Register

SECOND LIST - look up the two fields and return the value

So for the

first line 'Rare' & 'Minor' it would look to the second list, and return 'Low'

second line 'unlikely' & 'Moderate' it would look to the second list and return 'Medium'

third line 'Possible, and 'Major' it would look to the second list and return ' High'

Hopefully someone can help me.

Thanks!

Answer 1

Nutsy_23,

You can expand the formula box by clicking on the bottom right-hand corner of the box (you will see the 2 lines in the shape of a triangle) and then dragging to resize as appropriate.

Regards,

A

Answer 2

Dear Nutsy_23,

Thanks for your updates and sharing the solution here. We appreciate it as it may help other community members who are experiencing the similar issues.

Besides the long formula, the better way I can consider may be to use the dependent drop-down lists solution in Power Apps.

For example, you have one source list ListDemoB and another list ListDemoA for you and users to update the data based on ListDemoB.

In ListDemoA, Column1 is to populate Likelihood from ListDemoB, Choice1 is a single-line text column populate Major, Minor and Moderate from ListDemoB and Column2 is a single-line text column to populate values from Major, Minor and Moderate from ListDemoB.

You can create a canvas app as follows.

TextInput2 for Title: Default:""

Dropdown1 for Column1: Items: Distinct(ListDemoB,Likelihood)

Dropdown2 for Minor: Items: Distinct(Filter(ListDemoB,Likelihood=Dropdown1.Selected.Result),Minor)

Dropdown3 for Major: Items: Distinct(Filter(ListDemoB,Likelihood=Dropdown1.Selected.Result),Major)

Dropdown4 for Moderate: Items: Distinct(Filter(ListDemoB,Likelihood=Dropdown1.Selected.Result),Moderate)

Create three buttons.

Minor: OnSelect: Patch(ListDemoA,Defaults(ListDemoA),{Title:TextInput2.Text,Column1:Dropdown1.Selected.Result,Choice1:Label3.Text,Column2:Dropdown2.Selected.Result})

Major: OnSelect: Patch(ListDemoA,Defaults(ListDemoA),{Title:TextInput2.Text,Column1:Dropdown1.Selected.Result,Choice1:Label4.Text,Column2:Dropdown3.Selected.Result})

Moderate: OnSelect: Patch(ListDemoA,Defaults(ListDemoA),{Title:TextInput2.Text,Column1:Dropdown1.Selected.Result,Choice1:Label6.Text,Column2:Dropdown4.Selected.Result})

If users or you click on Minor and type the Title value, select the Column1 value, the corresponding Minor , Major and Moderate values will be popped up and update the Title value, the selected Column1 value, the Choice1 value and the Minor value in Column2 in ListDemoA.

In the same way, if you select Major or Moderate, the Major value and the Moderate value will be populated in Column2 as well.

Result:

For your reference: Create dependent drop-down lists in a canvas app

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save". ***

Answer 3

Hi Cliff, I ended up writing a very long... If statement. If works fine and at this stage. I won't be changing the table often but if you have a better outcome for next time that would be appreciated. Thanks :-)

=IF(OR(AND([Inherent Likelihood]="Rare",OR([Inherent Consequence]="Minimal",[Inherent Consequence]="Minor",[Inherent Consequence]="Moderate")),AND([Inherent Likelihood]="Unlikely",OR([Inherent Consequence]="Minimal",[Inherent Consequence]="Minor")),AND(OR([Inherent Likelihood]="Possible",[Inherent Likelihood]="Likely"),[Inherent Consequence]="Minimal")),"Low",IF(OR(AND([Inherent Likelihood]="Rare",OR([Inherent Consequence]="Major",[Inherent Consequence]="Severe")),AND([Inherent Likelihood]="Unlikely",OR([Inherent Consequence]="Moderate",[Inherent Consequence]="Major")),AND([Inherent Likelihood]="Possible",OR([Inherent Consequence]="Minor",[Inherent Consequence]="Moderate")),AND([Inherent Likelihood]="Likely",[Inherent Consequence]="Minor"),AND([Inherent Likelihood]="Almost Certain",OR([Inherent Consequence]="Minimal",[Inherent Consequence]="Minor"))),"Medium",IF(OR(AND([Inherent Likelihood]="Unlikely",[Inherent Consequence]="Severe"),AND([Inherent Likelihood]="Possible",OR([Inherent Consequence]="Major",[Inherent Consequence]="Severe")),AND([Inherent Likelihood]="Likely",OR([Inherent Consequence]="Moderate",[Inherent Consequence]="Major")),AND([Inherent Likelihood]="Almost Certain",[Inherent Consequence]="Moderate")),"High",IF(OR(AND([Inherent Likelihood]="Likely",[Inherent Consequence]="Severe"),AND([Inherent Likelihood]="Almost certain",OR([Inherent Consequence]="Major",[Inherent Consequence]="Severe"))),"Extreme","ERROR"))))

Answer 4

Hi Cliff, Thanks so much for that information. I'm in the process of upskilling in canvas/power apps so I will tackle your suggestion shortly. I really appreciate you following that up.

A suggestion for your development team, could they increase the size of the formula box in the Sharepoint lists, its difficult to put long formulas in.

Thanks again!

Answer 5

Dear Nutsy_23,

Thanks for reply and suggestions here.

As our product developers are willing to hear users' suggestions and ideas on product improvments, I sugget you submit your feedback on
the Feedback platform to convey your requirements to our related team as soon as possible. If our product developers have some updates on the feedback they have submitted, they will update the information there directly.

Sincerely,

Cliff | Microsoft Community Moderator