Lookupvalue on multivalued fields separated by commas

[samuelbruxel]

I'm trying for a few days to get this done right, so I thought I should ask for help in this forum.

So, I have 2 tables like this:

Table1 with 2 columns: OrderID(alphanumerical formatted as text) and Price($)
Table2 that has a column ServiceID (number), and Orders (same as OrderID, but formatted with Orders separated by commas, 1 to 10 orders usually)

Table1
OrderID Price
DW4500 $7540,00
DW4501 $2497,00
DW4502 $7961,00
DW4503 $672,00

Table2
ServiceID Orders
22701715 DW4500
22701716 DW4501,DW4502,DW4503

What I want is to SUM total value for that ServiceID.

At first I tried Lookupvalue for an extra column on Table1 to get the ServiceID and SUM after on a pivottable.
I can easily do that when the ServiceID has only ONE order.

I need to find a way to get that ServiceID into the corresponding row on Table2.

Anyone? Thanks in advance

 

[Deep Dave]

Here! Try This -

Put the Below Table in Range A1:B5

OrderID Price
DW4500 $7540,00
DW4501 $2497,00
DW4502 $7961,00
DW4503 $672,00

Put the below Table in D1:E3

[TABLE="width: 383"]
<tbody>[TR]
[TD]ServiceID[/TD]
[TD]Orders[/TD]
[/TR]
[TR]
[TD]22701715[/TD]
[TD]DW4500[/TD]
[/TR]
[TR]
[TD]22701716[/TD]
[TD]DW4501,DW4502,DW4503[/TD]
[/TR]
</tbody>[/TABLE]

And Put this Formula in Cell F2 and Commit using Ctrl + Shift + Enter. Drag It down as required.

=SUM(LOOKUP(MID(E2,CHOOSE(LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1,1,{1,8},{1,8,15},{1,8,15,22},{1,8,15,22,29}),6),$A$2:$A$5,$B$2:$B$5))

Hope it helps!

Deep

 

[Deep Dave]

BTW, Make sure those $ Signs aint Text. Put the Number in (Like 7540) and then Format it to currency if you like.

 

[mgirvin]

Great Formula Deep Dave!

 

[Deep Dave]

Thnx Mike !

Your the one who has taught me that Choose Function..

Cheers!

Deep