Multiple Lookups in Same Cell - PLEASE HELP :(

Not sure which function I should be using as I believe VLOOKUP has its limitations with my issue.

Here's the problem I'm having. I'm totaling the time a specific tool is used in making a specific pastry. Below is my table;

Apple Pie
Step#_Description___Time(min)
1____Make Dough___9
2____Insert Apples__8
3____Mix Together__7
4____Bake_________20
5____More frosting__10

I then, for instance, I want to see how many minutes my spatula was used in the Apple Pie process, so I place a vlookup function that will look like: "
Tool________UsedinSteps____Minutes used Total "
Spatula______1,2,5_________(vlookup fnct. here)

I told the VLOOKUP function to look in the cell to the left ( A COLUMN where the 1,2,5 are) and find the 1 in the table and spit out the 9min time, that I can do. Now, I didn't know how to tell vlookup how to find step 1 and step 2 and step 5 and add their totals (in this case 9min+8min+10min) and spit out 27min under the "minutes used total" column. This is where I am lost, can VLOOKUP (or should I be using another function) search for two or more different items/values(in this case step#) and then find the corresponding values and sum the results together, can it see the comma Delimitor ??? At the moment I have "vlookup___&" / "&vlookup___ ..."and that gives me back "9 / 8 / 10" but I cannot add that together because it sees it as one number. If anyone can point me in the right direction I would GREALTY sppreciate it, perhaps I'm using the incorrect function? Please let me know. Thanks in advance guys!

-J

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Here, try this:

Formula:

Please Login or Register  to view this content.

Note: works for maximum 1 to 10 jobs

You could use job starting from 10 (and not from 1).
That way you will be able to handle jobs with this formula from 10 to 99.
Or if starting job is 100 you can use jobs from 100 to 999.

Problem is here when you searching job 2, for example it will give you wrong result for 32, or 52,

Here's a way to do it with a UDF (User defined Function)
I am just learning VBA so can't vouch for it's ruggedness but this seems to work

Open up the VBA Editor (ALT + F11)
Insert a module
Paste this code in

Please Login or Register  to view this content.

Then in D12 for example
=SumTime(C12)
See attachment
Questions?

EDIT: Note the VLOOKUP range is in the code. You will need to change this to match your workbook.

I believe it would work for the active sheet. The issue is that you need to go into the formula (select the cell, F2, ENTER) to make it recalculate. Not sure if there's an easier way to do that.

Good question. I can't see why it wouldn't but it doesn't. I'll see if a VBA guy can look at this.

You could do that with formula but you would need be careful:

While 1st solution (1-9 jobs) return correct result no matter do you enter jobs 1,2,4 or 1, 2,4,

With 2nd solution you MUST put COMMA then SPACE between ALL jobs within one cell. Like this: 1, 11, 21, 4

It will work but I won't suggest you merged cell. 3 is 4th column (0,1,2,3). So if you have merged 3rd and 4th column you need to put 2.