Cross reference based on multiple columns

[l_blumberger]

Hi, 

As in the example below, I have table 1 with ID numbers of visitors and the pattern of stores they visited and table 2 with the distance between every combination of stores.

Table 1:

ID	Store
1	a
1	b
1	c
1	a

Table 2:

RowID	Origin	Destination	Distance
1	a	a	50
2	a	b	40
3	a	c	100
4	a	d	70

I need to add the distances from table 2 into table 1 depending on the patterns of stores visited. Ideally, the resulting table would like:

ID	Origin	Destination	Distance
1	a	b	40
1	b	c	60
1	c	a	100

I may need to do some rearranging of table 1 and something like a vlookup to reference table 2, but I am unsure of the best method to use. Any suggestions would be helpful.

Thanks!

[jgo · Accepted solution]

Hi @l_blumberger,

Would the attached workflow solve what you're trying to accomplish?

[JohnJPS · Accepted solution]

Not sure this is "correct" but I attached a workflow which:

• Uses a MultiRow formula to generate the "Dest" column based on the following row's store (grouped by ID)
• Generates a lookup key based on translating "b to a" to "a to ba" based on a < b, (so the join will work if Orig is "b" and Dest is "a")

Then just joins... hope it helps!