I have two Excel tables:
When there is a Tag with the value of ProcessID, I would like to populate the column OrderID in table Process with OrderIDs from table Orders.
I was looking into Excel arrays, so if needed, I know how to change the Tags to something like {217,218,229,230}.
This is the result I'm looking for:
After encoutering issues with solution proposed in answers below - for larger field sizes (Tags), I thought I found another solution which I posted here, but I'm deleting it now as that didn't work properly as I later figured it out.
I ended up adding the first and last bracket to the Tags string and then I brought my data to Access where I was able to easily achieve what I wanted with a simple SQL query:
UPDATE Processes, Orders
SET Processes.OrderID = [Orders].[ID]
WHERE (([Orders].[Tags] Like "*|" & [Processes].[ID] & "|*"));
You can do this with a formula:
=IFERROR(INDEX(OrderTab!A:A,MATCH("*|"&A2&"|*","|"&OrderTab!B:B&"|",0)),"Not Found")
Assuming your Order table is on a worksheet called OrderTab
. Change this.
Result: