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

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
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
    Capture_1st attachment for 1st tab.PNG
    13.3 KB · Views: 1
  • Capture_2nd attachment for source data tab.PNG
    Capture_2nd attachment for source data tab.PNG
    28.5 KB · Views: 1

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top