Search within a range in dependance of another column and retrun something else :-)

[JasXel]

Hi guys,

I am trying to search a date within a range in dependance of a personnel number and return the corresponsing working %. Can you please help?
PN Working% From To
1000 50% 01.01.2016 30.06.2017
1000 100% 01.07.2017 31.12.2018
1005 30% 01.01.2010 31.12.2016
1005 100% 01.01.2017 31.12.2020

PN Date Working %
1000 01.05.2016 SEARCH
1000 01.08.2017 SEARCH
1005 01.05.2015 SEARCH
1005 01.09.2019 SEARCH



Excel is attached.

Thanks a lot.

[Glenn Kennedy]

Maybe:

=AVERAGEIFS(B:B,A:A,K2,C:C,"<="&L2,D:D,">="&L2)

except that ypu will probably need ; and not , as a separator.

[JasXel]

Thanks, but unfortunately that did not work. Maybe I was not clear in my initial post

PN Date Working %
1000 01.05.2016 SEARCH for PN 1000 and date 01.05.2016 in the range of C and D, but only for that particular PN and then return the working %, which is in this case 50%

[Glenn Kennedy]

See what is in the attached sheet, and then supply expected answers for the four cases.

[JasXel]

YESSSS!!! My appologies for not getting it sooner. That is what I was looking for, thank you so much!