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
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 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | App ID | Name2 | Ver | NameVersion | Version ID | Unique | Published | Published | Year | Month | Date | Latest | Expected Result | |||
2 | 30 | tes-Chk-aws-next-service | 3 | tes-Chk-aws-next-service-3 | 12006887 | 839811 tes-Chk-aws-next-service-3 | 2021-05-07 09.45.41 | 2021-05-07 09.45.41 | 2021 | May | 07 | Yes | Yes | |||
3 | 31 | tes-Chk-aws-next-service | 3 | tes-Chk-aws-next-service-3 | 12006887 | 839811 tes-Chk-aws-next-service-3 | 2021-05-07 09.45.41 | 2021-05-07 09.45.41 | 2021 | May | 07 | Yes | Yes | |||
4 | 32 | tes-Chk-aws-next-service | 3 | tes-Chk-aws-next-service-3 | 12006887 | 839811 tes-Chk-aws-next-service-3 | 2021-05-07 09.45.41 | 2021-05-07 09.45.41 | 2021 | May | 07 | Yes | Yes | |||
5 | 33 | tes-Chk-aws-next-service | 3 | tes-Chk-aws-next-service-3 | 12006887 | 839811 tes-Chk-aws-next-service-3 | 2021-05-07 09.45.41 | 2021-05-07 09.45.41 | 2021 | May | 07 | Yes | Yes | |||
6 | 34 | tes-Chk-aws-next-service | 2 | tes-Chk-aws-next-service-2 | 11997100 | 839811 tes-Chk-aws-next-service-2 | 2021-05-07 01.18.50 | 2021-05-07 01.18.50 | 2021 | May | 07 | Yes | No | |||
7 | 35 | tes-Chk-aws-next-service | 2 | tes-Chk-aws-next-service-2 | 11997100 | 839811 tes-Chk-aws-next-service-2 | 2021-05-07 01.18.50 | 2021-05-07 01.18.50 | 2021 | May | 07 | Yes | No | |||
8 | 36 | tes-Chk-aws-next-service | 2 | tes-Chk-aws-next-service-2 | 11997100 | 839811 tes-Chk-aws-next-service-2 | 2021-05-07 01.18.50 | 2021-05-07 01.18.50 | 2021 | May | 07 | Yes | No | |||
9 | 37 | tes-Chk-aws-next-service | 2 | tes-Chk-aws-next-service-2 | 11997100 | 839811 tes-Chk-aws-next-service-2 | 2021-05-07 01.18.50 | 2021-05-07 01.18.50 | 2021 | May | 07 | Yes | No | |||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M2:M9 | M2 | =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: