Compare tables and find closed date prior to selection

[Paradox_R]

Hi All,

I'm currently doing scientific work and i can't find a way to find to match 2 table to find the closest date prior to the selection. I was wondering whether you experts can help me.
As the data i'm working with is confidential i'm making a dummy table to show what i would need.

What I need to fill in is the closest matching time prior to time sit in H4, H5, ect. So the function would have to compare Table 1 with Table 2 find a row containing with exact the same Fruit, Table, Position, Chair condition. This would have multiple options and from this it has to find the closest date prior to time sit (F4,F5).

I tried to use Vlookup function but i can't figure it out as we have multiple dates for a combination.

Table 1: Client time sit
CONCAT	Fruit	Table	Position	Chair	Time sit		Closest matching time prior to time sit
Apple0300002043 Alpha 1 , Position 3Big chair	Apple	0300002043	Alpha 1 , Position 3	Big chair	17/08/2021 23:05​		XX
Apple0300002043 Alpha 1 , Position 3small chair	Apple	0300002043	Alpha 1 , Position 3	small chair	18/08/2021 11:11​		XX
Table 2: Clients entered
CONCAT	Fruit	Table	Position	Chair	Time client came in
Apple0300002043 Alpha 1 , Position 3Big chair	Apple	0300002043	Alpha 1 , Position 3	Big chair	17/06/2021 11:11​
Apple0300002043 Alpha 1 , Position 3Big chair	Apple	0300002043	Alpha 1 , Position 3	Big chair	17/08/2021 22:11​
Apple0300002043 Alpha 1 , Position 3Big chair	Apple	0300002043	Alpha 1 , Position 3	Big chair	23/08/2021 10:05​
Apple0300002043 Alpha 1 , Position 3small chair	Apple	0300002043	Alpha 1 , Position 3	small chair	29/08/2021 11:11​
Apple0300002043 Alpha 1 , Position 3small chair	Apple	0300002043	Alpha 1 , Position 3	small chair	20/08/2021 20:11​
Apple0300002043 Alpha 1 , Position 3small chair	Apple	0300002043	Alpha 1 , Position 3	small chair	11/08/2021 15:11​
Apple0300002043 Alpha 1 , Position 3small chair	Apple	0300002043	Alpha 1 , Position 3	small chair	17/08/2021 9:11​

Hoping there is a way that this is possible.

Thanks a lot in advance to help!

 

[asjmoron]

Try this (assuming your table starts at A1)

=MAXIFS(F10:F16,A10:A16,A4)

 

[Paradox_R]

Try this (assuming your table starts at A1)

=MAXIFS(F10:F16,A10:A16,A4)

Hi asjmoron,

I tried the formulla but unfortunately i only get the output 0, so i is unfortunately not working