Sharepoint List - VLOOKUP function

Question

We have a Sharepoint List with several columns containing different data. Two of the the columns are: 'Product' and 'Price'. The information in the column 'Product' will not be entered at the time when the item is created, but it will be added at a later stage. When the information 'Product' is added for the item we need the 'Price' to be auto populated with the corresponding price retrieved in another Sharepoint List (or Excel list on the same Sharepoint site). Similar to the VLOOKUP function in Excel (find and retrieve the Price for the specific Product). Is this possible?

Answer 1

Hi Eva_1,

I go through the post carefully, it seems that you want to use Vlookup formula in Calculated Field in SharePoint List. However, the formula or formula with similar feature could be not available in Calculated Field now(For all available Formula in Calculated column, please check the article: Alphabetical list of functions----Calculated Column) I would suggest you vote and leave your comments in thread with hundreds of votes in SharePoint UserVoice: Allow lookup columns brought up from different list into calculated columns. Your voice will promote the related team to develop the feature in SharePoint Online. Appreciate your efforts and understanding!

As a workaround, you may create a flow in Power Automate. Suppose you have two lists. List1 and List2. You add items to “Product” Column in List1 and would like to Get Items from “Price” Column in List2 when “Product” name is the same.  Steps below may help:

1. In SharePoint list, Click +Add column and show ID column, find ID of related Product.
2. Login in Power Automate website: https://flow.microsoft.com
3. Crate an Automate Flow
4. Add Action: When an Item is created or Modified. Enter List1 here.

 

5. Add Action: Get Item, Enter List2(where save price information) here.

 

6. Add action **Update Item, in ID filed, enter ID from When an Item is created or Modified Action.**In Price Filed, Enter Price column from Get Item Action (List2).

 

7. Overview of the flow:

 

Hope these can help.

Regards,

Clark

Answer 2

Hi Eva_1,

Have you checked information above? Welcome to post back if there are any updates.

Regards,

Clark

Answer 3

hi Clart. I am trying to create the same process but can I not use the unique ID?

Answer 4

I used this flow and it worked until I had multiple items assigned with the same value. Is there a way to allow duplicates?