VLOOKUP look for last cell

[LordEvil]

Hi all,

I have a workbook that has two sheets. Sheet1 and Sheet2.
Sheet 1 will have employee names in column "C"
The name will appear on sheet 2 in column "C" as well
The times I want to lookup are in columns G and H in sheet2

I am using this formula below in column M and N in Sheet1 to find the first line in and out. T

=IFERROR(VLOOKUP(D15,Sheet2!C:I,5,FALSE),"")
and
=IFERROR(VLOOKUP($D15,Sheet2!C:H,6,FALSE),"")

In sheet 2, the name will appear on 2 lines. This is where I am getting stumped. I'm thinking I need to use an index formula for the 2nd set of times but not sure how to write that.

This is how the data looks on sheet 2

C F G H
Employee Work Date IN OUT
[MCCOY, PAUL] 11/13/2019 5:54AM 11:31AM <------- Formula I am using will find this
[MCCOY, PAUL] 11/13/2019 12:01PM 2:33PM

I would like to lookup in sheet 2 for the second set of values under the matching name and post the values in sheet1 column O15 and P15. I hope I explained this well enough. Any help would be appreciated. Thanks all!

 

[Peter_SSs]

If the 2 rows with the name in question are consecutive as it appears from your sample then try this for the second one

=INDEX(Sheet2!G$1:G$1000,MATCH(D15,Sheet2!C$1:C$1000,0)+1)
(Note I generally prefer not to use whole column references)

If the names can be in non-consecutive rows then

=INDEX(Sheet2!G$1:G$1000,AGGREGATE(15,6,(ROW(Sheet2!G$1:G$1000)-ROW(Sheet2!G$1)+1)/(Sheet2!C$1:C$10000=D15),2))

 

[LordEvil]

That was it Peter, Thank You!

 

[Peter_SSs]

That was it Peter, Thank You!

You're welcome. Thanks for the follow-up.