COUNTIF problem

[Ian Beath]

Hi, I am trying to return a value based on a range after a COUNTIF, e.g.

Sheet 1 has the range E6:E34 which lists employee IDs that have worked nightshift in a month
Sheet 1 also has a range M6:M34 which lists the number of nights worked
Sheet 2 has all possible employees by employee ID, i.e. criteria A3:A119

Once I execute the countif, I know they have worked a nightshift, but how do I return the corresponding value from the range M6:M34?

Thanks in advance, this is driving me nuts.

Cheers

 

[Ian Beath]

forget to mention I'm using 2003 (unfortunately)

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this formula in B3 of Sheet2
=IF(COUNTIF(Sheet1!$E$6:$E$34,A3),INDEX(Sheet1!$M$6:$M$34,MATCH(A3,Sheet1!$E$6:$E$34,0)),0)
copy down till B119 (last row with data)

Hope this helps

M.

 

[Ian Beath]

Thanks Marcelo, I'll give it a try.

 

[Ian Beath]

Works like a charm. You're a jet Marcelo!!

 

[Marcelo Branco]

You are welcome. Glad to help

M.