Excel 2007 : If statement to return a text value

[retroedward]

hi there,

I'm trying to find a formula which will return a text value from a data set I'm using...

to explain myself better - I have data along the lines of

Fields (columns):

Job Number, Employee name, Job title, Week commencing

Sample data:

xy1234, Pete, Director, 01/08/11

I want a formula that uses job number, job title and week commencing as the variables, and gives the Employee name as the result.

I've tried lots of combinations of IF, AND, INDEX, MATCH, VLOOKUP etc and they all seem to work only for the first instance in the list and then either give no result or repeat the first result.

It seems so obviously easy in my head, I just can't seem to get it running. Any help would be greatly appreciated!

many thanks
Ed

[Bob Phillips]

Try this aarray formula

=INDEX($B$2:$B$200,MATCH(1,($A$2:$A$200="xy1234")*($C$2:$C$200="Director")*($D$2:$D$200=--"2011-08-01"),0))

[retroedward]

works a treat! amazing, thanks so much Bob.

Try this aarray formula

=INDEX($B$2:$B$200,MATCH(1,($A$2:$A$200="xy1234")*($C$2:$C$200="Director")*($D$2:$D$200=--"2011-08-01"),0))