MATCH Function - Multiple Criteria

Hi,

Need a bit of help with a match formula. I have data on employees and their benefits. They can have a pension and one of three other benefits (Gym/Medical/Free Lunch). I need my match formula to search for the employee number and then one of the additional benefits.

I have this formula: {=MATCH(E2&E3,CODE&ADDITIONAL_Pay,0)} which will return the correct information, for example if the employee number is "3333" and they have a "free lunch" - it pulls out 13, the correct row location.

What I need to find out, is how I can amend the formula so that if the employee number is "3333" and "Free Lunch / Gym / Medical" are present then the correct benefit is returned. The staff member can only have one additional benefit in excess of their pension.

I've tried lots of ways, with IF /OR but none seem to work. I also seem to encounter the problem that my Match formulae only seems to find the first instances of the employee number.

I've attached a simplified version of my problem.
MATCH example.xlsx

Hello,

Thank, that works, I think I see what you have done with the +1, i.e shifted the row down one. However, I realised my file gave the wrong impression.

People can opt out of the pension, so it won't always be there.

Sometimes there will be other bits of data in the list so potentially, for each employee their could be 3 entries, 4, 2, 1 etc. So, as much as your solution is spot on, I don't think it will work in the situation I find myself in.

I've updated the file:

MATCH example.xlsx

Thanks for the help.