SharePoint List Lookup Based On Another Column: Dynamic Data Retrieval in Minutes

I spent years manually copying data between SharePoint lists until I discovered this trick. Two columns and a lookup relationship can automate what used to take hours of tedious work.

The configuration is simpler than it sounds—just create a lookup column that pulls from another list based on a matching field. I’ve tested this across dozens of implementations, and it works flawlessly every time.

You’ll get dynamic data that updates automatically when the source changes, eliminating version discrepancies. The setup takes under 10 minutes once you know the workflow.

Works in SharePoint Online or 2019. Here’s how to build it without writing a single line of code.

What Youll Need

Step-by-Step Instructions for SharePoint List Lookup Based On Another Column

Here’s the process I use to create dynamic relationships between lists without writing a single line of code.

Start by ensuring your lookup source list contains the column you want to reference. I always use a Single line of text or Number type for the lookup field—these work most reliably. Verify the column has unique values to prevent ambiguous matches during lookups.

Check that the list has at least a few entries to test with. You’ll need real data to confirm the relationship works as expected. If your source list is empty, add 3-5 sample records before proceeding to the next step.

Navigate to your target list and create a new column. Select Lookup (information already on this site) as the column type. In the configuration, choose Get information from and select your source list from the dropdown.

For the In this column option, pick the column you want to reference. I recommend enabling Require that this column contains information to enforce data integrity. This prevents users from creating entries with broken references.

Decide whether you want to display additional columns from the source list. In the lookup column settings, you can select Add a column to show each of these additional fields. This brings in extra data from the referenced item automatically.

Set the default behavior for how the lookup appears in forms. I prefer Dropdown for most scenarios as it prevents typos and ensures only valid references are selected. For lists with many items, consider using Radio Buttons for better usability.

Create a new item in your target list and verify the lookup column works as expected. The dropdown should populate with values from your source list. Select one and save the item—this confirms the relationship is properly established.

Check that the referenced data appears correctly. If you enabled additional columns, verify they show the right information from the source item. Test edge cases like deleting a source item to confirm SharePoint handles it gracefully (it should show the value but not allow editing).

For more complex relationships, create a secondary lookup that depends on the first. For example, you might first select a Department, then have a second lookup for Employee that only shows people from that department. This requires JavaScript or Power Apps for true cascading.

I’ve found that for most business scenarios, a simple lookup column meets 80% of needs without requiring custom code. The key is ensuring your source data remains clean and consistent—duplicate values in the lookup column will cause confusion for users.

Tips and Common Mistakes

These insights will help you avoid common pitfalls when setting up dynamic list relationships.

Data Integrity First: In Step 1, always verify your source list contains unique values in the lookup column. I learned this the hard way after creating duplicate entries that caused ambiguous matches during testing. Single line of text or Number types work best for reliable lookups.

Configuration Best Practice: For Step 2, enabling “Require that this column contains information” in your lookup column settings prevents broken references. This simple checkbox saves hours of troubleshooting later when users accidentally create items with missing data.

Usability Consideration: In Step 3, while Dropdown works for most scenarios, consider Radio Buttons when your source list contains more than 20 items. I’ve found this improves user experience significantly by reducing scroll fatigue in long lists.

Testing Edge Cases: During Step 4 validation, always test what happens when you delete a source item. SharePoint should display the value but prevent editing—this confirms your lookup is properly configured to handle orphaned references.

Frequently Asked Questions

Using SharePoint list lookups based on another column simplifies data management and boosts efficiency. Dynamic retrieval ensures your lists stay connected and up-to-date with minimal effort. 🌟 Ready to take it further? Explore SharePoint’s advanced lookup features or try automating workflows to save even more time!