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

Dear Nutsy_23,

Thanks for your updates.

I may need some time to do a test in our environment and I'll update here as soon as possible.

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 4

Dear Nutsy_23

Good day! Thank you for posting in Microsoft Community. We are happy to help you.

Based on your description, we understand you want to look up another list based on the column values in one list in SharePoint.

If my understanding is correct, we kindly suggest you create a list relationship by using lookup columns to achieve this requirement. In a list relationship, information from one list is automatically shown in another list, which lets you join information from two lists and keep it consistent while people edit and delete list items.  And the target list items can also link to and display multiple columns worth of information from the source list, and relationships can change what happens when an item is deleted.

To make a list relationship, you will need two lists: the source list and the target list. You should make the source list first. All the detailed steps for the process are included in the article, you can follow the steps in the article to create the list relationships to lookup the list based the column values. For your reference: Create list relationships by using lookup columns (microsoft.com)

We’re looking forward to your reply and will continue to help you all the time!  If there are any misunderstanding or unclear, you can post back in your free time.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Stacey | 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 5

Hi, Yes I have successfully used look-up before for a single column's value, but correct me if I am wrong the standard lookup doesn't allow you to use two separate values from two separate columns from the target list to then lookup the value from the source list's column and then row in the second list. Is this possible with look-up or is this something that I require Powerapps for?

Thanks