Returning a text value that matches 2 conditions

[Pauljj]

I have worked this out how to return a numerical value but not a text value

In column B is the employees payroll number, in column G is the date they start their leave and in column H is when they finish their leave

On another sheet Is 'Employee A's payroll number in cell E5 with a date in G4

What I want is, if E5 can be found in Column B and if G4 can be found in column H then it should return the value from column D

The payroll number in B and the dates in column G can appear muliple times, so this should only return a value if both date and payroll number match.. Any ideas please ?

 

[Domski]

Try something like:

=INDEX(Sheet1!$D$2:$D$100,MATCH(1,(Sheet1!$B$2:$B$100=E5)*(Sheet1!$H$2:$H$100=G4),0))

Confirmed with Ctrl+Shift+Enter as it's an array formula.

Dom

 

[Pauljj]

Thanks Domski, perfect