Index match vs. If/countifs

[that_one_girl]

I have a need that I can’t seem to find a resolution to.
I need a formula that will

2. look in Training Records workbook, Column E for a name in the Staff List workbook, tab1 Column A.
3. 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.
4. 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, 5^th column – is employee frombook 2, pg. 1, 1^st 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, 3^rd 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.

 

[RasGhul]

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?

 

[that_one_girl]

I can.

2. I need to find the name in Book2 tab 1 column A, in Book1 column E.
3. 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
4. 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

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

START DATES​

​

TOTAL STAFF​

% COMPLETE​

2​

10/1/2017​

10/1/2018​

1​

Yr %​

21​

43%​

9​

​

​

3​

10/1/2015​

10/1/2018​

3​

Plus Years %​

76​

26%​

20​

​

4​

10/1/2015​

10/1/2018​

3​

Plus Yrs Tech%​

76​

26%​

20​

​

5​

NAME​

CSHO/EE ID​

CLASS​

UNIT​

REGION​

STATE STATUS​

OFFICE​

HIRE DATE​

2 CLASSES​

1 YR CALC.​

3 YR CALC.​

Orientation To Enforcement​

Introduction to Health Standards​

Introduction to Safety Standards​

Introduction to Construction and Electrical Standards​

6​

CROCKER, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

10/1/13​

COMPLETED​

COMPLETED​

COMPLETED​

​

7​

DE GUZMAN, S​

B5240​

ASE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

9/30/17​

COMPLETED​

COMPLETED​

COMPLETED​

​

8​

DIAZ, O​

O1907​

ASE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

11/1/17​

COMPLETED​

COMPLETED​

COMPLETED​

​

9​

GARNER, K​

J6921​

DM​

ENF​

1​

CURRENT​

AMERICAN CANYON​

5/30/02​

​

​

​

​

10​

KHERADPIR, S​

N1374​

ASE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

10/1/05​

COMPLETED​

​

​

​

<tbody> </tbody>

BOOK2, tab2, Column A

TECHNICAL CLASS

Agricultural Safety and Health

Applied Welding Principles

Certified Safety Professional Exam Preparation

Combustible Dust

Concrete Forms and Shoring

Construction Safety

Cranes and Rigging

Demolition

DOSH Policy and Procedures Manual Page 11 of 16

Electrical Safety

Emergency Response

Excavation, Trenching and Soil Mechanics

Fall Arrest Systems

Fire Protection

Hazardous Materials

Heat Illness

Logging Safety

Machine Guarding

Permit-Required Confined Spaces

Principles of Scaffolding

Process Safety Management

Steel Erection

Accident Investigation

Aerosol Transmissible Disease

Agricultural Safety and Health

Analytical Methods

Applied Spray Finishing and Coating Principals

Applied Welding Principles

Bloodborne Pathogens

Certified Industrial Hygienist Exam Preparation

Emergency Response

Heat Illness

IH Sampling

Indoor Air Quality

Industrial Noise

Industrial Toxicology

Permit-Required Confined Spaces

Principles of Ergonomics

Principles of Industrial Ventilation

Respiratory Protection

<tbody> </tbody>

TABLE 1

A​

B​

C ​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N ​

6​

DATE​

TYPE​

SUBJECT​

HRS​

NAME​

CSHO/EE ID​

CLASS​

UNIT​

Region​

STATUS​

Office​

Hire Date​

Separation Date​

Re-Hire Date​

7​

10/06/14​

IN​

CITATION WRITING​

6.0​

CROCKER, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

8​

10/22/14​

OUT​

SCAFFOLDING SAFETY​

16.0​

ESKANDAR, M​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

9​

12/02/14​

IN​

ACCIDENT INVESTIGATION​

21.5​

FORT, L​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

10​

01/23/15​

IN​

NEW HIRE TRAINING 2015 - SESSION 2​

3.0​

BROWN, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

11​

01/27/15​

OUT​

MACHINE GUARDING & LOTO​

24.0​

OBERT, B​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

12​

03/02/15​

IN​

ORIENTATION TO ENFORCEMENT​

32.0​

PATNAIK, D​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

13​

03/18/15​

IN​

TITLE 8 REGULATION - PART 1​

3.0​

SEKHON, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

14​

04/07/15​

IN​

HEAT ILLNESS​

3.0​

CROCKER, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

15​

04/22/15​

IN​

ADULT FIRST AID/CPR/AED ​

4.0​

FORT, L​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

16​

05/20/15​

OUT​

MEASLES: IT'S NOT JUST A RASH​

3.0​

BROWN, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

17​

06/08/15​

IN​

INSPECTION TECHNIQUES AND LEGAL ASPECTS​

35.0​

OBERT, B​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

18​

07/15/15​

IN​

FINDING & INTERPRETING THE RIGHT TITLE 8 REGULATION - PART 1​

3.0​

PATNAIK, D​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A​

19​

07/28/15​

OUT​

EXCAVATION, TRENCHING AND SOIL MECHANICS​

16.0​

CROCKER, J​

P7724​

SSE​

ENF​

1​

CURRENT​

AMERICAN CANYON​

N/A​

N/A​

N/A ​

<tbody> </tbody>

 

[RasGhul]

Could this help?

Book1
	A	B	C
1	STAFF	TRAINING RECORDS Match?	2 or more
2	JASON	Yes	TRUE
3	DAVID	Yes	TRUE
4	MICHELLE	Yes
5	TONY	No
6	FRED	Yes
7	AMBER	Yes
8	MACY	Yes
Sheet1

Cell Formulas
Range	Formula
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
1	Training
2	JASON
3	DAVID
4	MICHELLE
5	TONY
Sheet2

Book1
	A	B	C	D	E
1	Date	Lecturer	STAFF	Time	STAFF
2			JASON		JASON
3			DAVID		DAVID
4			MICHELLE		MICHELLE
5			ZARA		ZARA
6			JASON		JASON
7			DAVID		DAVID
8			FRED		FRED
9			AMBER		AMBER
10			MACY		MACY
11			MACY		MACY
Sheet1

 

[RasGhul]

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?

 

[that_one_girl]

$I$4 of Book2 tab1.

The third data set should say BOOK1 *sorry about that

 

[RasGhul]

You could use a helper column O/Book1 Table with;

=COUNTIF([Book2.xlsx]Sheet2!$A$2:$A$41,C7)

This should return 2 for Heat Illness

Then use this in I7 and copy down;

=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..

 

[that_one_girl]

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?

 

[RasGhul]

Yes if you want to avoid helper columns and such vba will be the way to go.

 

[that_one_girl]

I don't know VBA..........What a pain. Thank you for your help.