[SOLVED] Looking up a date for an employee in a list of employees and date ranges

[FCFalkirk]

I have a list of employees, each with a number of date ranges showing the employee salary point at different times. I have another list of employee numbers with a particular date. I need to look up the salary point on the first list for that date for that employee.

I have an example but I'm have problems uploading.

Can anybody help with this? I can find help on looking up a date in a list of date ranges, but I have to find the right range for the particular employee.

Can anybody please help?

[mdimran.ahmed30]

Hi,

can you attache some sample file.

Imran.

[JohnTopley]

To upload file:

Click "Go Advanced" the either "Paper Clip" icon or "Manage attachments"

Click "Add files"

"Browse" - select you file

Click "Upload" the "Done".

It helps those who respond if they have data to work with.

Thank you.

[FCFalkirk]

Thanks. I'm trying to upload an example but it doesn't work. It's only a 10kb file.

[FCFalkirk]

Sample file now added below.

[FCFalkirk]

I now have attached this sample. Can anybody please help?

[JohnTopley]

Try

in C4

=INDEX($D$14:$D$63,MATCH(1,($A$14:$A$63=$A5)*($B$14:$B$63<=$B5)*($C$14:$C$63>=$B5),0))

Enter with Ctrl+Shift+Enter

Copy down

[FCFalkirk]

Thank you very much, John. This does exactly what I was looking for. Much appreciated.

[JohnTopley]

Thank you for the feedback.