Lookup value to match three criteria and return closely matching for the third

[p1nkman]

Hey guys,

I have been struggling with this problem for a while now in Excel. Realized its easier to work in PBI but I can't figure out a way to do it.

As shown in the example picture, I have a big list of data with similar repetitions of same ULID. I need to exact match the ITMCOD, ULID, DATE and then return the nearest timestamp for that result.

Would anybody know how this can be done?

Best regards

[p1nkman]

@Fowmy Hey I saw you reply to a similar thread in the past. Unfortunately I couldn't understand the solution that you posted for this other solution. Could you please maybe have a look at mine?

Thank you!

[p1nkman]

@serpiva64thanks for the reply. Here is the sample dataset:

A

CODE	PALETTE	DATETIME
91511352	0034260033126760377	9/15/2022 1:28:29 AM
91511352	0034260033126760377	9/15/2022 1:35:05 AM
91511352	0034260033126760377	9/15/2022 11:13:38 PM
91511356	3,426E+16	9/17/2022 10:13:38 PM

B

CODE	PALETTE	DATETIME
91511352	0034260033126760377	15/09/2022 01:45:33
91511352	0034260033126760377	15/09/2022 02:48:48
91511352	0034260033126760377	15/09/2022 05:48:02
91511352	0034260033126760377	15/09/2022 23:48:05
91511352	0034260033126760377	16/09/2022 01:40:05

[serpiva64]

Sorry but i can't find 23:36:27 in your table A

[p1nkman]

Sorry about that, we can assume 9/15/2022 11:13:38 PM instead of 23:36:27. Either ways it should match to 15/09/2022 23:48:05 from B.

@serpiva64  

[serpiva64]

Hi,

As you see from the image below i got the result

I think you need to try it with more data.

Take a look to the attached file

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[p1nkman]

Hi @serpiva64 ,

Thank you so much for your effort. Unfortunately it doesn't work with my dataset which is fairly large. Was my mistake setting up the sample data.

Would you have a suggestion if I just want to match with a condition of a timeframe that it matches the nearest data within next 3 hours? I think that might work too in my case. Appreciate your time. 

[p1nkman]

@serpiva64 

I realize the problem occurs at the last step, when I remove the duplicates. It sometimes removes the right one. Example:

We have two matches here. Second one would be the correct match. But when I remove duplicate on the last step, it removes the second one. 

[serpiva64]

Hi,

can you post some data that cause this problem

[serpiva64]

Hi,

i thought there might be some problems with real data.

I foreseen the problem of sorting and i will try to solve it.

[p1nkman]

I don't know why it doesn't show the table border. Basically first eight digit is the CODE, then again the date starts from 9 on Table A and 15 on Table B

[serpiva64]

Hi,

please post your sample data not as an image and with some more rows if possible