Index match vs. If/countifs

that_one_girl

New Member
Joined
Mar 22, 2017
Messages
43
I have a need that I can’t seem to find a resolution to.
I need a formula that will

  1. look in Training Records workbook, Column E for a name in the Staff List workbook, tab1 Column A.
  2. If that name is in both locations, then search all records with that staff name in Training Records workbook, Column C, against the list of classes in Staff List workbook, tab2 Column A.
  3. If it finds that employee in the Training Records workbook, with 2 or more of the classes listed in Staff List workbook tab2 Column A, then populate with “TRUE”, if not, leave blank.
SO:
Look in book 1, 5th column – is employee frombook 2, pg. 1, 1st column listed here? (YES)

Ok doesthat employee have 2 or more of the classes listed in book 2, pg. 2, firstcolumn listed next to their name in book 1, 3rd column? (YES)
Ok – fill cell with “TRUE”
If not, leave blank.
L Anyone able to help? I’ve tried VLookup, butthe problem with that is that the employee name is to the right of the courselist in book 1. I don’t understand Index Match enough to create a formula. Ialso tried IF with AND but that didn’t work either.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi,

I think maybe COUNTIFS will serve you best as your main goal is to find a match that occurs more than once. Also just to clarify that you need to look between different Workbooks and not different sheets in the same workbook?

Can you post a sample of your data so we can best design a formula?
 
Last edited:
Upvote 0
I can.

  1. I need to find the name in Book2 tab 1 column A, in Book1 column E.
  2. If they are there, then I need to make sure their hire date in Book2 tab1 column H is equal to or greater the Start Date in B3 of same sheet
  3. If that is all true, then search Book1 column C for 2 courses that match Book2, tab2 column A.
If all of that is there, “TRUE” if not, leave the cellblank.
BOOK2, tab 1
[TABLE="width: 719"]
<tbody>[TR]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"]
A
[/TD]
[TD="width: 75, bgcolor: transparent"]
B
[/TD]
[TD="width: 56, bgcolor: transparent"]
C
[/TD]
[TD="width: 39, bgcolor: transparent"]
D
[/TD]
[TD="width: 53, bgcolor: transparent"]
E
[/TD]
[TD="width: 60, bgcolor: transparent"]
F
[/TD]
[TD="width: 68, bgcolor: transparent"]
G
[/TD]
[TD="width: 59, bgcolor: transparent"]
H
[/TD]
[TD="width: 48, bgcolor: transparent"]
I
[/TD]
[TD="width: 52, bgcolor: transparent"]
J
[/TD]
[TD="width: 42, bgcolor: transparent"]
K
[/TD]
[TD="width: 78, bgcolor: transparent"]
L
[/TD]
[TD="width: 76, bgcolor: transparent"]
M
[/TD]
[TD="width: 76, bgcolor: transparent"]
N
[/TD]
[TD="width: 78, bgcolor: transparent"]
O
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
1
[/TD]
[TD="width: 73, bgcolor: transparent"]
[/TD]
[TD="width: 131, bgcolor: transparent, colspan: 2"]
START DATES

[/TD]
[TD="width: 39, bgcolor: transparent"]
[/TD]
[TD="width: 53, bgcolor: transparent"]
[/TD]
[TD="width: 60, bgcolor: transparent"]
TOTAL STAFF
[/TD]
[TD="width: 68, bgcolor: transparent"]
% COMPLETE
[/TD]
[TD="width: 59, bgcolor: transparent"]
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
2
[/TD]
[TD="width: 73, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
10/1/2017
[/TD]
[TD="width: 56, bgcolor: transparent"]
10/1/2018
[/TD]
[TD="width: 39"]
1
[/TD]
[TD="width: 53"]
Yr %
[/TD]
[TD="width: 60"]
21
[/TD]
[TD="width: 68, bgcolor: transparent"]
43%
[/TD]
[TD="width: 59, bgcolor: transparent"]
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
9
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
3
[/TD]
[TD="width: 73, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
10/1/2015
[/TD]
[TD="width: 56, bgcolor: transparent"]
10/1/2018
[/TD]
[TD="width: 39"]
3
[/TD]
[TD="width: 53"]
Plus Years %
[/TD]
[TD="width: 60"]
76
[/TD]
[TD="width: 68, bgcolor: transparent"]
26%
[/TD]
[TD="width: 59, bgcolor: transparent"]
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
20
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
4
[/TD]
[TD="width: 73, bgcolor: transparent"]
[/TD]
[TD="width: 75, bgcolor: transparent"]
10/1/2015
[/TD]
[TD="width: 56, bgcolor: transparent"]
10/1/2018
[/TD]
[TD="width: 39"]
3
[/TD]
[TD="width: 53"]
Plus Yrs Tech%
[/TD]
[TD="width: 60"]
76
[/TD]
[TD="width: 68, bgcolor: transparent"]
26%
[/TD]
[TD="width: 59, bgcolor: transparent"]
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
20
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 76, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
5
[/TD]
[TD="width: 73"]
NAME
[/TD]
[TD="width: 75"]
CSHO/EE ID
[/TD]
[TD="width: 56"]
CLASS
[/TD]
[TD="width: 39"]
UNIT
[/TD]
[TD="width: 53"]
REGION
[/TD]
[TD="width: 60"]
STATE STATUS
[/TD]
[TD="width: 68"]
OFFICE
[/TD]
[TD="width: 59"]
HIRE DATE
[/TD]
[TD="width: 48"]
2 CLASSES
[/TD]
[TD="width: 52"]
1 YR CALC.
[/TD]
[TD="width: 42"]
3 YR CALC.
[/TD]
[TD="width: 78"]
Orientation To Enforcement
[/TD]
[TD="width: 76"]
Introduction to Health Standards
[/TD]
[TD="width: 76"]
Introduction to Safety Standards
[/TD]
[TD="width: 78"]
Introduction to Construction and Electrical Standards
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
6
[/TD]
[TD="width: 73, bgcolor: transparent"]
CROCKER, J
[/TD]
[TD="width: 75, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 56, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 39, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 53, bgcolor: transparent"]
1
[/TD]
[TD="width: 60, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 68, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 59, bgcolor: transparent"]
10/1/13
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 78"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
7
[/TD]
[TD="width: 73, bgcolor: transparent"]
DE GUZMAN, S
[/TD]
[TD="width: 75, bgcolor: transparent"]
B5240
[/TD]
[TD="width: 56, bgcolor: transparent"]
ASE
[/TD]
[TD="width: 39, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 53, bgcolor: transparent"]
1
[/TD]
[TD="width: 60, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 68, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 59, bgcolor: transparent"]
9/30/17
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 78"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
8
[/TD]
[TD="width: 73, bgcolor: transparent"]
DIAZ, O
[/TD]
[TD="width: 75, bgcolor: transparent"]
O1907
[/TD]
[TD="width: 56, bgcolor: transparent"]
ASE
[/TD]
[TD="width: 39, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 53, bgcolor: transparent"]
1
[/TD]
[TD="width: 60, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 68, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 59, bgcolor: transparent"]
11/1/17
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 78"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
9
[/TD]
[TD="width: 73, bgcolor: transparent"]
GARNER, K
[/TD]
[TD="width: 75, bgcolor: transparent"]
J6921
[/TD]
[TD="width: 56, bgcolor: transparent"]
DM
[/TD]
[TD="width: 39, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 53, bgcolor: transparent"]
1
[/TD]
[TD="width: 60, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 68, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 59, bgcolor: transparent"]
5/30/02
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78"]
[/TD]
[TD="width: 76"]
[/TD]
[TD="width: 76"]
[/TD]
[TD="width: 78"]
[/TD]
[/TR]
[TR]
[TD="width: 27, bgcolor: transparent"]
10
[/TD]
[TD="width: 73, bgcolor: transparent"]
KHERADPIR, S
[/TD]
[TD="width: 75, bgcolor: transparent"]
N1374
[/TD]
[TD="width: 56, bgcolor: transparent"]
ASE
[/TD]
[TD="width: 39, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 53, bgcolor: transparent"]
1
[/TD]
[TD="width: 60, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 68, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 59, bgcolor: transparent"]
10/1/05
[/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 52, bgcolor: transparent"]
[/TD]
[TD="width: 42, bgcolor: transparent"]
[/TD]
[TD="width: 78, bgcolor: transparent"]
COMPLETED
[/TD]
[TD="width: 76"]
[/TD]
[TD="width: 76"]
[/TD]
[TD="width: 78"]
[/TD]
[/TR]
</tbody>[/TABLE]

BOOK2, tab2, Column A
[TABLE="width: 200"]
<tbody>[TR]
[TD="width: 268, bgcolor: transparent"] TECHNICAL CLASS
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Agricultural Safety and Health
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Applied Welding Principles
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Certified Safety Professional Exam Preparation
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Combustible Dust
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Concrete Forms and Shoring
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Construction Safety
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Cranes and Rigging
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Demolition
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] DOSH Policy and Procedures Manual Page 11 of 16
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Electrical Safety
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Emergency Response
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Excavation, Trenching and Soil Mechanics
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Fall Arrest Systems
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Fire Protection
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Hazardous Materials
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Heat Illness
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Logging Safety
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Machine Guarding
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Permit-Required Confined Spaces
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Principles of Scaffolding
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Process Safety Management
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Steel Erection
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Accident Investigation
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Aerosol Transmissible Disease
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Agricultural Safety and Health
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Analytical Methods
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Applied Spray Finishing and Coating Principals
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Applied Welding Principles
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Bloodborne Pathogens
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Certified Industrial Hygienist Exam Preparation
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Emergency Response
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Heat Illness
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] IH Sampling
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Indoor Air Quality
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Industrial Noise
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Industrial Toxicology
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Permit-Required Confined Spaces
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Principles of Ergonomics
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Principles of Industrial Ventilation
[/TD]
[/TR]
[TR]
[TD="width: 268, bgcolor: transparent"] Respiratory Protection
[/TD]
[/TR]
</tbody>[/TABLE]

TABLE 1
[TABLE="width: 729"]
<tbody>[TR]
[TD="width: 25, bgcolor: transparent"][/TD]
[TD="width: 55"]
A
[/TD]
[TD="width: 36"]
B
[/TD]
[TD="width: 313"]
C
[/TD]
[TD="width: 33, bgcolor: transparent"]
D
[/TD]
[TD="width: 77, bgcolor: transparent"]
E
[/TD]
[TD="width: 54, bgcolor: transparent"]
F
[/TD]
[TD="width: 41, bgcolor: transparent"]
G
[/TD]
[TD="width: 37, bgcolor: transparent"]
H
[/TD]
[TD="width: 45, bgcolor: transparent"]
I
[/TD]
[TD="width: 56, bgcolor: transparent"]
J
[/TD]
[TD="width: 62, bgcolor: transparent"]
K
[/TD]
[TD="width: 36, bgcolor: transparent"]
L
[/TD]
[TD="width: 63, bgcolor: transparent"]
M
[/TD]
[TD="width: 53, bgcolor: transparent"]
N
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
6
[/TD]
[TD="width: 55"]
DATE
[/TD]
[TD="width: 36"]
TYPE
[/TD]
[TD="width: 313"]
SUBJECT
[/TD]
[TD="width: 33"]
HRS
[/TD]
[TD="width: 77"]
NAME
[/TD]
[TD="width: 54"]
CSHO/EE ID
[/TD]
[TD="width: 41"]
CLASS
[/TD]
[TD="width: 37"]
UNIT
[/TD]
[TD="width: 45"]
Region
[/TD]
[TD="width: 56"]
STATUS
[/TD]
[TD="width: 62"]
Office
[/TD]
[TD="width: 36"]
Hire Date
[/TD]
[TD="width: 63"]
Separation Date
[/TD]
[TD="width: 53"]
Re-Hire Date
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
7
[/TD]
[TD="width: 55"]
10/06/14
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
CITATION WRITING
[/TD]
[TD="width: 33, bgcolor: transparent"]
6.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
CROCKER, J
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
8
[/TD]
[TD="width: 55"]
10/22/14
[/TD]
[TD="width: 36"]
OUT
[/TD]
[TD="width: 313"]
SCAFFOLDING SAFETY
[/TD]
[TD="width: 33, bgcolor: transparent"]
16.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
ESKANDAR, M
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
9
[/TD]
[TD="width: 55"]
12/02/14
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
ACCIDENT INVESTIGATION
[/TD]
[TD="width: 33, bgcolor: transparent"]
21.5
[/TD]
[TD="width: 77, bgcolor: transparent"]
FORT, L
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
10
[/TD]
[TD="width: 55"]
01/23/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
NEW HIRE TRAINING 2015 - SESSION 2
[/TD]
[TD="width: 33, bgcolor: transparent"]
3.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
BROWN, J
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
11
[/TD]
[TD="width: 55"]
01/27/15
[/TD]
[TD="width: 36"]
OUT
[/TD]
[TD="width: 313"]
MACHINE GUARDING & LOTO
[/TD]
[TD="width: 33, bgcolor: transparent"]
24.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
OBERT, B
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
12
[/TD]
[TD="width: 55"]
03/02/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
ORIENTATION TO ENFORCEMENT
[/TD]
[TD="width: 33, bgcolor: transparent"]
32.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
PATNAIK, D
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
13
[/TD]
[TD="width: 55"]
03/18/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
TITLE 8 REGULATION - PART 1
[/TD]
[TD="width: 33, bgcolor: transparent"]
3.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
SEKHON, J
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
14
[/TD]
[TD="width: 55"]
04/07/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
HEAT ILLNESS
[/TD]
[TD="width: 33, bgcolor: transparent"]
3.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
CROCKER, J
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
15
[/TD]
[TD="width: 55"]
04/22/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
ADULT FIRST AID/CPR/AED
[/TD]
[TD="width: 33, bgcolor: transparent"]
4.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
FORT, L
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
16
[/TD]
[TD="width: 55"]
05/20/15
[/TD]
[TD="width: 36"]
OUT
[/TD]
[TD="width: 313"]
MEASLES: IT'S NOT JUST A RASH
[/TD]
[TD="width: 33, bgcolor: transparent"]
3.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
BROWN, J
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
17
[/TD]
[TD="width: 55"]
06/08/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
INSPECTION TECHNIQUES AND LEGAL ASPECTS
[/TD]
[TD="width: 33, bgcolor: transparent"]
35.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
OBERT, B
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
18
[/TD]
[TD="width: 55"]
07/15/15
[/TD]
[TD="width: 36"]
IN
[/TD]
[TD="width: 313"]
FINDING & INTERPRETING THE RIGHT TITLE 8 REGULATION - PART 1
[/TD]
[TD="width: 33, bgcolor: transparent"]
3.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
PATNAIK, D
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
[TR]
[TD="width: 25, bgcolor: transparent"]
19
[/TD]
[TD="width: 55"]
07/28/15
[/TD]
[TD="width: 36"]
OUT
[/TD]
[TD="width: 313, bgcolor: transparent"]
EXCAVATION, TRENCHING AND SOIL MECHANICS
[/TD]
[TD="width: 33, bgcolor: transparent"]
16.0
[/TD]
[TD="width: 77, bgcolor: transparent"]
CROCKER, J
[/TD]
[TD="width: 54, bgcolor: transparent"]
P7724
[/TD]
[TD="width: 41, bgcolor: transparent"]
SSE
[/TD]
[TD="width: 37, bgcolor: transparent"]
ENF
[/TD]
[TD="width: 45, bgcolor: transparent"]
1
[/TD]
[TD="width: 56, bgcolor: transparent"]
CURRENT
[/TD]
[TD="width: 62, bgcolor: transparent"]
AMERICAN CANYON
[/TD]
[TD="width: 36, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 63, bgcolor: transparent"]
N/A
[/TD]
[TD="width: 53, bgcolor: transparent"]
N/A
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Could this help?


Book1
ABC
1STAFFTRAINING RECORDS Match?2 or more
2JASONYesTRUE
3DAVIDYesTRUE
4MICHELLEYes
5TONYNo
6FREDYes
7AMBERYes
8MACYYes
Sheet1
Cell Formulas
RangeFormula
B2=IF(COUNTIFS('[Training Records.xlsx]Sheet1'!$E$2:$E$11,A2)>0,"Yes","No")
C2=IF(COUNTIFS('[Training Records.xlsx]Sheet1'!$C$2:$C$11,Sheet2!A2)>1,"TRUE","")



Book1
A
1Training
2JASON
3DAVID
4MICHELLE
5TONY
Sheet2



Book1
ABCDE
1DateLecturerSTAFFTimeSTAFF
2JASONJASON
3DAVIDDAVID
4MICHELLEMICHELLE
5ZARAZARA
6JASONJASON
7DAVIDDAVID
8FREDFRED
9AMBERAMBER
10MACYMACY
11MACYMACY
Sheet1
 
Upvote 0
Ok the Post #3 Sample data and goal is different from your original so we will need to modify to suit.

Where do you want the formula output to be?
 
Upvote 0
You could use a helper column O/Book1 Table with;
Code:
=COUNTIF([Book2.xlsx]Sheet2!$A$2:$A$41,C7)

This should return 2 for Heat Illness

Then use this in I7 and copy down;
Code:
=IF(AND(COUNTIFS(H7,">="&$B$3)>0,COUNTIFS([Book1.xlsx]Sheet1!$E$7:$E$19,A7,[Book1.xlsx]Sheet1!$O$7:$O$19,">="&2)=1),"True","")

But none of the sample names would be True if we were testing against your Hire Date and Start Date B3, would you like to test multiple course dates from Book1 table within your Course Start & Finish dates B3 & C3 instead?

EG CROCKER, J 04-07-15 Heat Illness would be True..
 
Upvote 0
No.....our federal requirements are stringent. We have to audit staff that were hired on or before 10/1/15 and ensure that they have taken at least 2 of the listed courses. I'm beginning to wonder if perhaps a VBA code would be better suited?
 
Upvote 0
Yes if you want to avoid helper columns and such vba will be the way to go.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top