Vlookup based on certain criteria

[Tashat]

Hello Everyone

I'm looking for some help with an If statement and vlookup (I think). I would like to use a formula in column B, which looksup using the Role ID in Column A, against the role ID in column D and then returns the name of the employee. However, I have repeated role IDs in column D, because some employees have resigned and a new employee has replaced them and been assigned the same role ID. I can't delete these resigned employees because I need to keep this historical information in the sheet. Is there a way I can use an if statement (or similar) to ignore the role-if if column F shows as resigned? So I only return the name of the current employee in column B?

I use Excel 365. Thank you in advance.

Book1
	A	B	C	D	E	F
1	Role_ID	Employee Name		Role-ID	Employee Name	Status
2	XE-CEC1			XE-CEC1	Amy	Current
3	XE-CEB1			XE-CEB1	Fred	Resigned
4	XC-CEH1			XE-CEB1	Anne	Current
5	XL-CEB1			XL-CEB1	Mo	Current
6	XJ-CEB1			XJ-CEB1	Sam	Current
7	XH-CEA1			XH-CEA1	Alf	Resigned
8	XH-CEK1			XH-CEA1	Sarah	Current
9	XE-CEA1			XC-CEK1	Naomi	Current
10	XC-CEK1			XK-CEB1	Matt	Resigned
11	XK-CEB1			XK-CEB1	Lucas	Current
12	XH-CED1			XH-CED1	Danielle	Resigned
13	XJ-CED1			XH-CED1	Jeremy	Current
14	XH-CEJ1			XH-CEJ1	John	Current
15	XH-CEB1			XH-CEB1	Andrea	Current
Sheet1

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Role_ID	Employee Name		Role-ID	Employee Name	Status
2	XE-CEC1	Amy		XE-CEC1	Amy	Current
3	XE-CEB1	Anne		XE-CEB1	Fred	Resigned
4	XC-CEH1			XE-CEB1	Anne	Current
5	XL-CEB1	Mo		XL-CEB1	Mo	Current
6	XJ-CEB1	Sam		XJ-CEB1	Sam	Current
7	XH-CEA1	Sarah		XH-CEA1	Alf	Resigned
8	XH-CEK1			XH-CEA1	Sarah	Current
9	XE-CEA1			XC-CEK1	Naomi	Current
10	XC-CEK1	Naomi		XK-CEB1	Matt	Resigned
11	XK-CEB1	Lucas		XK-CEB1	Lucas	Current
12	XH-CED1	Jeremy		XH-CED1	Danielle	Resigned
13	XJ-CED1			XH-CED1	Jeremy	Current
14	XH-CEJ1	John		XH-CEJ1	John	Current
15	XH-CEB1	Andrea		XH-CEB1	Andrea	Current
Master

Cell Formulas
Range	Formula
B2:B15	B2	=XLOOKUP(A2&"|Current",$D$2:$D$15&"|"&$F$2:$F$15,$E$2:$E$15,"")

 

[Tashat]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Role_ID	Employee Name		Role-ID	Employee Name	Status
2	XE-CEC1	Amy		XE-CEC1	Amy	Current
3	XE-CEB1	Anne		XE-CEB1	Fred	Resigned
4	XC-CEH1			XE-CEB1	Anne	Current
5	XL-CEB1	Mo		XL-CEB1	Mo	Current
6	XJ-CEB1	Sam		XJ-CEB1	Sam	Current
7	XH-CEA1	Sarah		XH-CEA1	Alf	Resigned
8	XH-CEK1			XH-CEA1	Sarah	Current
9	XE-CEA1			XC-CEK1	Naomi	Current
10	XC-CEK1	Naomi		XK-CEB1	Matt	Resigned
11	XK-CEB1	Lucas		XK-CEB1	Lucas	Current
12	XH-CED1	Jeremy		XH-CED1	Danielle	Resigned
13	XJ-CED1			XH-CED1	Jeremy	Current
14	XH-CEJ1	John		XH-CEJ1	John	Current
15	XH-CEB1	Andrea		XH-CEB1	Andrea	Current
Master

Cell Formulas
Range	Formula
B2:B15	B2	=XLOOKUP(A2&"|Current",$D$2:$D$15&"|"&$F$2:$F$15,$E$2:$E$15,"")

Thank you Fluff- as always. Stunning formula! I've never seen a formula which uses | before! I can't even unpick what each part of this formula is doing. Usually I can work out the logic, but not with this one.

 

[Fluff]

The | is just used as a separator to ensure that the correct values are found, although it's not really needed here, I tend to use it any way.

With the data below, if you concatenate the 2values together without a separator all the rows are the same, but with the | then they are still distinct

+Fluff v2.xlsm
	H	I	J	K
1
2	abc	def	1	1
3	ab	cdef	1	2
4	a	bcdef	1	3
Master

Cell Formulas
Range	Formula
J2:J4	J2	=MATCH(H2&I2,$H$2:$H$4&$I$2:$I$4,0)
K2:K4	K2	=MATCH(H2&"|"&I2,$H$2:$H$4&"|"&$I$2:$I$4,0)

 

[Tashat]

The | is just used as a separator to ensure that the correct values are found, although it's not really needed here, I tend to use it any way.

With the data below, if you concatenate the 2values together without a separator all the rows are the same, but with the | then they are still distinct

+Fluff v2.xlsm
	H	I	J	K
1
2	abc	def	1	1
3	ab	cdef	1	2
4	a	bcdef	1	3
Master

Cell Formulas
Range	Formula
J2:J4	J2	=MATCH(H2&I2,$H$2:$H$4&$I$2:$I$4,0)
K2:K4	K2	=MATCH(H2&"|"&I2,$H$2:$H$4&"|"&$I$2:$I$4,0)

Oh yes - I remember using | now many moons ago when I used Lotus 123! Blimey! Thank you.

 

[Fluff]

You're welcome.