HOW TO ADD CRITERIA TO EXISTING "INDIRECT(VLOOKUP" FORMULA

[JuicyMusic]

These INDIRECT formula's are absolutely testing me, but I'm going to learn how to adjust them if it's the last thing I do….with your help, of course.
I can see now that I need to add a criteria to my formula. I will try my best to explain.

Question 1: Is it possible for the formula in column R of the 1st attachment/tab to bring back all the job numbers that the employee, named in column B of the source tab, worked on instead of just the first occurrence that it finds there? Can it bring back the result as a string? Example: "Job# 9358A, Job# 9405J"-----------------AS LONG AS the text "Activity on Job" is in column D of the target row on the source data tab.

AaronA is an excellent example. His name was listed twice on the source tab. His name was simply included in a note for Job #9358A. He didn't actually didn't work on job #9358A. It was just a note and should not be counted. You can see the text "No activity on Job" in column D of the corresponding row......….His name IS listed on an active job - and the text in column D for that row is "Activity on Job". This means that he did work on Job #9405J...….SO, this is the job number that should be returned in column R on the 1st attachment/tab...…SO counted in column T as "1".



Formula in column R:

=IF(OR(Q10="Off/Vacation",Q10="--"),"--",VLOOKUP("*"&$C10&"*",INDIRECT(Q$8&"!$A:$B"),2,0))

Formula in column T:

=IF(Q10="Off/Vacation","--",
IF(ISERROR(COUNTIFS(INDIRECT(Q$8&"!M:M"),"*"&Q10&"*")),"--",COUNTIFS(INDIRECT(Q$8&"!M:M"),"*"&Q10&"*")))

Sorry that I can't use the XLBB here at work. I've tried and I've been denied. I hope that attachment images are acceptable.

The 1st attachment/tab: This spreadsheet shows us what job numbers employees worked on - by day. 126 Employees are listed here and they work on ONE job 99.9% of the time. Only a handful will work on more than one job in a day. I filtered this tab by the two example employees because this sheet is very long. See the formula in column

The 2nd attachment/tab: The source data for the 1st attachment is on this tab. The wildcard formula on the other tab looks for employee names is in this tab, in column M.
I filtered this tab so you can see a portion of it and easily see the issue I'm having with 2 employees as an example. I did this because this tab is 4000 rows long.

Thank you so much.

 

[steve the fish]

You cant use vlookup to do multiple condition lookups (unless you use helper columns). Something like:

=INDEX(B:B,MATCH(1,INDEX((ISNUMBER(SEARCH(C10,A:A)))*(D:D="Activity on job"),0),0))

You can add in the indirects but if get stuck come back. Preferably dont use full column referencing.