VLOOKUP look for last cell

LordEvil

New Member
Joined
Oct 20, 2018
Messages
16
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!
 
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,700
Office Version
365
Platform
Windows
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))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,390
Messages
5,468,322
Members
406,579
Latest member
lollypop1389

This Week's Hot Topics

Top