lookup formula Find the Latest Set of records --

Z

zaveedd

New Member
Joined
Apr 23, 2021
Messages
12
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi,

I have an excel sheet that has the data of the report where a particular service is scanned at different times of the day/month and defines the threat level of the component in that Service. There are a few hundred services.

I was provided with a lookup formula which does the above but it does not consider the date and time. if the same service has multiple scans in the same day, it is marking all the records as Yes , which should not be the case.it should also take into consideration the time when the service was scanned and provide the result.

How can the above formula be modified to this?

In the attached excel sheet ( rows 30-38) tes-Chk-aws-next-service has ran twice on 5th july , 1 at 09:45:41 and another one at 01.1850. It should yes only to records that ran at09:45:42
Output2 ans (1).xlsx
ABCDEFGHIJKLMN
1App IDName2VerNameVersionVersion IDUniquePublishedPublishedYearMonthDateLatestExpected Result
230tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
331tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
432tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
533tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
634tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
735tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
836tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
937tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
Sheet3
Cell Formulas
RangeFormula
M2:M9M2=IF($L2=LOOKUP(2,1/($B$2:$B$10899=B2)/($K$2:$K$10899=$K2)/($I$2:$I$10899=$I2),$L$2:$L$10899),"Yes","No")
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
I think incorporating time into the equation you can simplify the formula to

=IF(H2=MAX(($B$2:$B$10899=B2)*($H$2:$H$10899)),"Yes","No")

Book3
ABCDEFGHIJKLMN
1App IDName2VerNameVersionVersion IDUniquePublishedPublishedYearMonthDateLatestExpected Result
230tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
331tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
432tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
533tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
634tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
735tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
836tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
937tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
Sheet1
Cell Formulas
RangeFormula
M2:M9M2=IF(H2=MAX(($B$2:$B$10899=B2)*($H$2:$H$10899)),"Yes","No")
 
Upvote 0
Crystalyzer said:
I think incorporating time into the equation you can simplify the formula to

=IF(H2=MAX(($B$2:$B$10899=B2)*($H$2:$H$10899)),"Yes","No")

Book3
ABCDEFGHIJKLMN
1App IDName2VerNameVersionVersion IDUniquePublishedPublishedYearMonthDateLatestExpected Result
230tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
331tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
432tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
533tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-07-05 09:45:412021-07-05 09:45:412021May7YesYes
634tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
735tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
836tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
937tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-07-05 01:18:502021-07-05 01:18:502021May7NoNo
Sheet1
Cell Formulas
RangeFormula
M2:M9M2=IF(H2=MAX(($B$2:$B$10899=B2)*($H$2:$H$10899)),"Yes","No")
Click to expand...

Unfortunately the formula is not working. I am using Microsoft Excel 2010 not sure if this makes any difference.
Output2 ans (1).xlsx
ABCDEFGHIJKLMN
1App IDName2VerNameVersionVersion IDUniquePublishedPublishedYearMonthDateLatestExpected Result
230tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
331tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
432tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
533tes-Chk-aws-next-service3tes-Chk-aws-next-service-312006887839811 tes-Chk-aws-next-service-32021-05-07 09.45.412021-05-07 09.45.412021May07YesYes
634tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
735tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
836tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
937tes-Chk-aws-next-service2tes-Chk-aws-next-service-211997100839811 tes-Chk-aws-next-service-22021-05-07 01.18.502021-05-07 01.18.502021May07YesNo
Sheet3
Cell Formulas
RangeFormula
N2:N5N2=IF(H2=MAX(($B$2:$B$10899=B2)*($H$2:$H$10899)),"Yes","No")
M2:M9M2=IF(H2=MAX(($B$2:$B$10899=B2)*($H$2:$H$10899)),"Yes","No")
 
Upvote 0
Try entering the formula as an array formula using CTL-SHFT-ENTER
 
Upvote 0
Crystalyzer said:
Try entering the formula as an array formula using CTL-SHFT-ENTER
Click to expand...
Thank you.
Its weird but the formula works in one sheet which has limited number of rows, but it fails when i apply it into another sheet that has thousands of rows. See below pic.


Any idea why this might be happening?
Inconsistency.jpg
 
Upvote 0
can you post the data using XL2BB? I know you won't be able to post it all, but perhaps enough that I can look into it?
 
Upvote 0
When i try to create a sample sheet with only some rows of data, result is showing up correctly, that is why unable to share a sample sheet :(

Looks like its the number of records that it has to search,name and the month.

Even if i change the service name to Test or something else the result shows up correctly.
One observation is it is happening only for July Month.
I am guessing it might have something to do with the name or the month.

Appreciate you for the help, let me know if there is any thing that i can check in terms of formatting or other options.

Crystalyzer said:
can you post the data using XL2BB? I know you won't be able to post it all, but perhaps enough that I can look into it?
Click to expand...
 
Last edited:
Upvote 0
Crystalyzer said:
can you post the data using XL2BB? I know you won't be able to post it all, but perhaps enough that I can look into it?
Click to expand...
Book1
ABCDEFGHIJKLMN
1App IDNameHas Month in Name Name 2 Starts with Letter or NumberV2Version IDPublishedYearQtrMonthDateService Last Published Service SlotLatest 2
21195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
31195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
41195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
51195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
61195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
71195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
81195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
91195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
101195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
111195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
121195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
131195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
Sheet3
Cell Formulas
RangeFormula
C2:C13C2=IF(SUMPRODUCT(--ISNUMBER(SEARCH(RangeMonth,B2))),"Yes","No")
D2:D13D2=IF(ISERR(LEFT(B2,1)*1),"letter","number")
E2:E13E2=MID(R2,MIN(MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},$R2)),"",FIND({1;2;3;4;5;6;7;8;9;0},$R2))),IFERROR(SEARCH("#",R2),155)),255)
H2:H13H2=TEXT(G2,"yyy")
I2:I13I2=IF(MONTH(G2)<=3,"Q1 (Jan-Mar)",IF(MONTH(G2)<=6,"Q2 (Apr-Jun)",IF(MONTH(G2)<=9,"Q3 (Jul-Sept)",("Q4 (Oct-Dec)"))))
J2:J13J2=TEXT(G2,"mmm")
K2:K13K2=TEXT(G2,"dd")
L2:L13L2=NOW()-G2
M2:M13M2=IF(L2>730,"2+ Years",IF(L2>365,"1-2 Years",IF(L2>270,"10-12 Months",IF(L2>180,"7-9 Months",IF(L2>90,"4-6 Months",IF(L2>60,"2-3 Months",IF(L2>30,"1-2 Months","<30 Days")))))))
N2:N13N2=IF(G2=MAX(($B$2:$B$14=B2)*($G$2:$G$14)),"Yes","No")
Press CTRL+SHIFT+ENTER to enter array formulas.


Please see the above, If you remove rows 10-13 ,Result in N6-N9 will change to Yes.
 
Upvote 0
Book1
ABCDEFGHIJKLMN
1App IDNameHas Month in Name Name 2 Starts with Letter or NumberV2Version IDPublishedYearQtrMonthDateService Last Published Service SlotLatest 2
21195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
31195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
41195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
51195190Test1Noletter49131576832021-07-20 06.30.462021Q3 (Jul-Sept)Jul2024<30 DaysNo
61195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
71195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
81195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
91195190Test1Noletter51133332092021-07-30 01.39.502021Q3 (Jul-Sept)Jul3014<30 DaysNo
101195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
111195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
121195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
131195190Test1Noletter59134299302021-08-04 23.12.232021Q3 (Jul-Sept)Aug048<30 DaysYes
141195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
151195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
161195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
171195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
181195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
191195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
201195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
211195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
221195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
231195193Test 4Noletter27131576262021-07-20 08.44.512021Q3 (Jul-Sept)Jul2024<30 DaysYes
241195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
251195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
261195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
271195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
281195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
291195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
301195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
311195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
321195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
331195193Test 4Noletter28133331472021-07-30 01.43.132021Q3 (Jul-Sept)Jul3014<30 DaysNo
Sheet3
Cell Formulas
RangeFormula
C2:C33C2=IF(SUMPRODUCT(--ISNUMBER(SEARCH(RangeMonth,B2))),"Yes","No")
D2:D33D2=IF(ISERR(LEFT(B2,1)*1),"letter","number")
E2:E33E2=MID(R2,MIN(MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},$R2)),"",FIND({1;2;3;4;5;6;7;8;9;0},$R2))),IFERROR(SEARCH("#",R2),155)),255)
H2:H33H2=TEXT(G2,"yyy")
I2:I33I2=IF(MONTH(G2)<=3,"Q1 (Jan-Mar)",IF(MONTH(G2)<=6,"Q2 (Apr-Jun)",IF(MONTH(G2)<=9,"Q3 (Jul-Sept)",("Q4 (Oct-Dec)"))))
J2:J33J2=TEXT(G2,"mmm")
K2:K33K2=TEXT(G2,"dd")
L2:L33L2=NOW()-G2
M2:M33M2=IF(L2>730,"2+ Years",IF(L2>365,"1-2 Years",IF(L2>270,"10-12 Months",IF(L2>180,"7-9 Months",IF(L2>90,"4-6 Months",IF(L2>60,"2-3 Months",IF(L2>30,"1-2 Months","<30 Days")))))))
N2:N33N2=IF(G2=MAX(($B$2:$B$14=B2)*($G$2:$G$14)),"Yes","No")
Press CTRL+SHIFT+ENTER to enter array formulas.


Please use the above data, have added more rows of data where the issue is there.
If you remove rows 10-13 ,Result in N6-N9 will change to Yes. For Test it is showing the incorrect result, N24 - N 33 should have Yes, but it is picking up the one (N14-N23 ) and saying its the latest , which is incorrect.
Crystalyzer said:
can you post the data using XL2BB? I know you won't be able to post it all, but perhaps enough that I can look into it?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
270
skyh3ck
skyh3ck
J
  • Question
Need help with scheduling formulas
Replies
0
Views
466
jordan89m
J
S
  • Question
VBA- To Merge two sets based on a few criterias
2
Replies
13
Views
551
shansakhi
S
MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
394
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,214,833
Messages
6,121,867
Members
449,053
Latest member
Mesh

Share this page

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