Lookup Based on 2 criteria

[karmaimages]

Hello

I'm trying to perform a lookup but need to add 2 criteria for the lookup bit don't know how this could be done. I have 2 sheets in the workbook which look like the following (I'm unable to upload a copy as I can't add the addon to work laptop)

	A	B	C	D	E	F	G
1		Year​
2		2023​
3				Person	Department
4				Job Bloggs	**Insert dept using lookup**

	A	B	C
1	Jo Bloggs	2022
2	Jo Bloggs	2023	Sales
3	Jo Bloggs	2023	Sales
4

What I need to do with the lookup is lookup Jo Bloggs on Sheet 2 and pull in the Department where the year taken from Cell B2 matches against column B in sheet 2 and ignore anythng that isn't the year selected.

Any suggestions on how to do this?

 

[ExceLoki]

i'm assuming "Job Bloggs" is supposed to be "Jo Bloggs", or vice-versa? as long as the match?

 

[ExceLoki]

try this

=INDEX(Sheet2!C1:C3,MATCH(1,(Sheet2!A1:A3=D4)*(Sheet2!B1:B3=B2),0))