Multiple criteria lookup and calculation

[UP1DOWN2]

Hi,

I have a rather complex situation which I've listed below step by step. I've managed some progress with the combination of some IF and VLOOKUP's but not luck. I've created a sample workbook of the situation.

1. Use column B value and lookup an array of data related to the result in sheet 2.
2. Column A contains multiple different amounts of data in each cell, the data is basically amount of each type. (Structure in each cell will always remain the same ie. qty x type; qty x type; etc.
3. I require the total time for all the listed types per row, which means the time per type must per looked up from sheet 2 based on Family type then the time must be multiplied by the qty amount before the type in Column A (ie. 2 x 111), and then the total for all type added to give an overall time.

I am not even sure this is possible with formulas and might have to use VBA.

Please can anyone assist with a solution to this scenario.

Sheet 1

qty & Types	Family	Total time
1 x 111; 2 x 123; 5 x 222 <tbody> </tbody>	B <tbody> </tbody>
2 x 147 <tbody> </tbody>	A <tbody> </tbody>
5 x 147 <tbody> </tbody>	C
1 x 987; 1 x 444 <tbody> </tbody>	A <tbody> </tbody>
2 x 987 <tbody> </tbody>	A <tbody> </tbody>
3 x 456; 3 x 321 <tbody> </tbody>	C <tbody> </tbody>
1 x 987 <tbody> </tbody>	C <tbody> </tbody>
3 x 987; 1 x 111 <tbody> </tbody>	B <tbody> </tbody>
1 x 123 <tbody> </tbody>	A

<tbody>
</tbody>

Sheet 2

Family A <tbody> </tbody>		Family B <tbody> </tbody>		Family C <tbody> </tbody>
Type <tbody> </tbody>	Time <tbody> </tbody>	Type <tbody> </tbody>	Time <tbody> </tbody>	Type <tbody> </tbody>	Time <tbody> </tbody>
111 <tbody> </tbody>	0.2 <tbody> </tbody>	111 <tbody> </tbody>	1 <tbody> </tbody>	111 <tbody> </tbody>	0.1 <tbody> </tbody>
123 <tbody> </tbody>	0.3 <tbody> </tbody>	123 <tbody> </tbody>	5 <tbody> </tbody>	123 <tbody> </tbody>	0.8 <tbody> </tbody>
222 <tbody> </tbody>	2 <tbody> </tbody>	222 <tbody> </tbody>	0.1 <tbody> </tbody>	222 <tbody> </tbody>	78 <tbody> </tbody>
444 <tbody> </tbody>	5 <tbody> </tbody>	444 <tbody> </tbody>	52 <tbody> </tbody>	444 <tbody> </tbody>	95 <tbody> </tbody>
147 <tbody> </tbody>	4 <tbody> </tbody>	147 <tbody> </tbody>	5 <tbody> </tbody>	147 <tbody> </tbody>	45 <tbody> </tbody>
987 <tbody> </tbody>	6 <tbody> </tbody>	987 <tbody> </tbody>	4 <tbody> </tbody>	987 <tbody> </tbody>	2 <tbody> </tbody>
456 <tbody> </tbody>	7 <tbody> </tbody>	456 <tbody> </tbody>	8 <tbody> </tbody>	456 <tbody> </tbody>	6 <tbody> </tbody>
321 <tbody> </tbody>	8 <tbody> </tbody>	321 <tbody> </tbody>	6 <tbody> </tbody>	321 <tbody> </tbody>	4 <tbody> </tbody>

<tbody>
</tbody>

 

[njimack]

Can the data in Sheet 1 be split into multiple rows rather than being separated by semi-colons?
Also, can Sheet 2 be laid out as follows, which would be much more logical?

TYPE | FAMILY | TIME

 

[UP1DOWN2]

No data in Sheet 1 can not be formatted as it is obtain from another source. Sheet 2 data can be changed and proposed