Helper column to identify BAME employees (Yes/No).

[maxim642]

I would like to add a column to a dataset that quickly identifies whether a person is BAME, or not.

Below is an example of my dataset. I have listed all the different ethnicity categories below in column A. Where I have written "BAME", that ethnicity should return "Yes", everything else (including blanks) "No".

15.3.21 HR Dashboard ML.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Full Name	Employee Number	Job Title	Job Level	Salary	Company	Division	Department	Status	Employee Type	Manager Full Name	Start Date	Continuous Start Date	Leaving Date	Location	Gender	Email	Contract End Date	Standard Hours	Disabled	Car Allowance	Actual FTE	Cost Centre	Ethnicity	LoS Date	Full Time Y/N	Start Year	Start Month	Leave Year	Leave Month	LoS (Days)	Los (Years)	Actual Start Date	Formatted Leave Date	BAME Yes/No
2	Worker1	1	Job1	20	x	LTD1	Team1	Dep1	Current	Employee	Man1	1/13/11			London	Male	N/A		37.5	No	0	1	N/A	White - Any other background	3/15/21	Yes	2011	Jan			3714	10.2	1/13/11
3	Worker2	2	Job2	20	x	LTD1	Team1	Dep1	Current	Employee Fixed Term	Man1	3/1/19			London	Male	N/A		37.5	No	0	1	N/A	White - English, Welsh, Scottish, Northern Irish, British	3/15/21	Yes	2019	Mar			745	2.0	3/1/19
4	Worker3	3	Job3	20	x	LTD1	Team1	Dep1	Current	Employee	Man1	1/11/21			London	Male	N/A		37.5	No	0	1	N/A	White - English, Welsh, Scottish, Northern Irish, British	3/15/21	Yes	2021	Jan			63	0.2	1/11/21
5	Worker4	4	Job4	20	x	LTD1	Team1	Dep1	Current	Contractor	Man1	1/4/21			London	Female	N/A		37.5	No	0	1	N/A	Asian - Other	3/15/21	Yes	2021	Jan			70	0.2	1/4/21
6	Worker5	5	Job5	20	x	LTD1	Team1	Dep1	Former	Employee Fixed Term	Man1	1/1/12		7/15/20	London	Male	N/A		37.5	No	0	1	N/A	White - English, Welsh, Scottish, Northern Irish, British	7/15/20	Yes	2012	Jan	2020	Jul	3118	8.5	1/1/12	7/15/20
7	Worker6	6	Job6	20	x	LTD1	Team1	Dep1	Current	Employee	Man1	9/30/19			Brighton	Male	N/A		37.5	No	0	1	N/A		3/15/21	Yes	2019	Sep			532	1.5	9/30/19
8	Worker7	7	Job7	20	x	LTD2	Team2	Dep2	Current	Employee	Man2	10/1/19			Brighton	Male	N/A		38.5	No	0	1	N/A	Black - Caribbean	3/16/21	Yes	2020	Oct			532	1.5	10/1/19
9	Worker8	8	Job8	20	x	LTD3	Team3	Dep3	Current	Apprentice	Man3	10/2/19			Brighton	Male	N/A		39.5	No	0	1	N/A	Prefer not to disclose	3/17/21	Yes	2021	Nov			532	1.5	10/2/19
10	Worker9	9	Job9	20	x	LTD4	Team4	Dep4	Current	Employee	Man4	10/3/19			Brighton	Male	N/A		40.5	No	0	1	N/A		3/18/21	Yes	2022	Dec			532	1.5	10/3/19
11	Worker10	10	Job10	20	x	LTD5	Team5	Dep5	Current	Apprentice	Man5	10/4/19			Brighton	Male	N/A		41.5	No	0	1	N/A	Mixed - Asian	3/19/21	Yes	2023	Jan			532	1.5	10/4/19
12	Worker11	11	Job11	20	x	LTD6	Team6	Dep6	Current	Employee	Man6	10/5/19			Brighton	Male	N/A		42.5	No	0	1	N/A	White - English, Welsh, Scottish, Northern Irish, British	3/20/21	Yes	2024	Feb			532	1.5	10/5/19
13	Worker12	12	Job12	20	x	LTD7	Team7	Dep7	Current	Employee	Man7	10/6/19			Brighton	Male	N/A		43.5	No	0	1	N/A	Mixed - Any other mixed multiple ethnic background	3/21/21	Yes	2025	Mar			532	1.5	10/6/19
14	Worker13	13	Job13	20	x	LTD8	Team8	Dep8	Current	Employee Fixed Term	Man8	10/7/19			Brighton	Male	N/A		44.5	No	0	1	N/A	Asian - Bangladeshi	3/22/21	Yes	2026	Apr			532	1.5	10/7/19
15	Worker14	14	Job14	20	x	LTD9	Team9	Dep9	Current	Employee	Man9	10/8/19			Brighton	Male	N/A		45.5	No	0	1	N/A	White - English, Welsh, Scottish, Northern Irish, British	3/23/21	Yes	2027	May			532	1.5	10/8/19
16	Worker15	15	Job15	20	x	LTD10	Team10	Dep10	Current	Employee	Man10	10/9/19			Brighton	Male	N/A		46.5	No	0	1	N/A	Black - African	3/24/21	Yes	2028	Jun			532	1.5	10/9/19
17
18
19	Ethnicity Types
20	Arab	BAME
21	Asian - Bangladeshi	BAME
22	Asian - British	BAME
23	Asian - Indian	BAME
24	Asian - Other	BAME
25	Asian - Pakistani	BAME
26	Black - African	BAME
27	Black - British	BAME
28	Black - Caribbean	BAME
29	Black - Other	BAME
30	Chinese - British	BAME
31	Mixed - Any other mixed multiple ethnic background	BAME
32	Mixed - Asian	BAME
33	Mixed - Black Caribbean	BAME
34	Mixed - British	BAME
35	Mixed - Other	BAME
36	Prefer not to disclose	NO
37	White - Any other background	NO
38	White - English, Welsh, Scottish, Northern Irish, British	NO
39	White - Irish	NO
40		NO
Sheet1

 

[mart37]

You can use VLOOKUP.

 

[maxim642]

You can use VLOOKUP.

Good idea. I never thought of that. Thanks!!