If Vlookup Value is less than this column cell, return next vlookup Value that's higher than this column cell

WHGRexcel

New Member
Joined
Mar 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello Guys,

First time poster. Love the site. I have this advanced excel problem that hopefully you guys can help out. I have two tabs: First tab contains an entry for each unique account ID and each date(s) they saw a login error. The 2nd tab contains an entry for each unique accountID and each date(s) they successfully logged in.

I want to do the following: I want to understand how many users from the first tab exist in the 2nd tab, from there, return the closer successful login date that is greater than the unsuccessful login. For example:

1st Tab - Unsuccessful Logins
Unique ID (Column A)Login Unsuccessful (Column B)Dates (Column C)
654877​
Login Unsuccessful20210220
654877​
Login Unsuccessful20210225
126257Login Unsuccessful20210205
624982Login Unsuccessful20210207

2nd Tab - Successful Logins
Unique ID (Column A)Login Successful (Column B)Dates (Column C)
654877​
Login Successful20210201
654877​
Login Successful20210223
654877​
Login Successful20210227
126257Login Successful20210207
624982Login Successful20210205

On the next Column on the first Tab, I want:


Unique ID (Column A)Login Unsuccessful (Column B)Dates (Column C)Next Successful Login Date (Column D)
654877​
Login Unsuccessful2021022020210223 (Successful Login C3)
654877​
Login Unsuccessful2021022520210227 (Successful Login C4)
126257Login Unsuccessful2021020520210207
624982Login Unsuccessful20210207"no" [login value is lower than login error value]


Hopefully I gave you enough information. Any help would be greatly appreciated! Thanks!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

WHGRexcel

New Member
Joined
Mar 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
By the way, I know how to return the first or last value in a vlookup, but not sure how to add an If condition if the vlookup value is lower, return the next value.

I was playing around with If/Large/Vlookup, but I don't think it would work as intended (=IF(VLOOKUP(A6,'LOGIN SUCCESSFUL'!$A$2:$F$45857,3,0)>=E6,LARGE(VLOOKUP(A6,'LOGIN SUCCESSFUL'!$A$2:$F$45857,3,0),">"&J6),"no"))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,060
Messages
5,639,824
Members
417,117
Latest member
Ravi Pandey

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
Top