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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top