excelvbams-accessexcel-formulalookup

Move data to another table based on array value

I have two Excel tables:

Table Process:
enter image description here

Table Orders:
enter image description here

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:
enter image description here

MY OWN SOLUTION:

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] & "|*"));
Solution

  • 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:

    enter image description here