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

#### JuicyMusic

##### Board Regular
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:
Excel Formula:
``=IF(OR(Q10="Off/Vacation",Q10="--"),"--",VLOOKUP("*"&\$C10&"*",INDIRECT(Q\$8&"!\$A:\$B"),2,0))``

Formula in column T:
Excel Formula:
``````=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.

#### Attachments

• Capture_1st attachment for 1st tab.PNG
13.3 KB · Views: 1
• Capture_2nd attachment for source data tab.PNG
28.5 KB · Views: 1

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### steve the fish

##### Well-known Member
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.

Replies
6
Views
177
Replies
0
Views
78
Replies
22
Views
364
Replies
1
Views
58
Replies
5
Views
151

1,127,370
Messages
5,624,290
Members
416,018
Latest member
mirceaon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back