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

This is SO SO helpful. Thanks so much!!