Formula for Table/Row Linking based on a Time Period

[lucaskre8]

Hi,

I would like to access/use data from one row in a table, that must be within a specified time period (columns for start and end dates and where there is no End Date, it is assumed current), in other table using some formula. This is hard to explain, so I have attached a simplified Excel file which may offer some context and clarity to my question.

Please note, in this file there are a couple other issues I am trying to address, but the primary one is this time interval one.

Any help would be greatly appreciated.

Cheers,
Luke

[AliGW]

Where are we meant to be looking? Where have you manually mocked up expected results? Better to strip out anything that's not relevant.

[AliGW]

Oh, I think I see now.

Why not just this?

=VLOOKUP([@HoleID],Collar,3,0)

[lucaskre8]

Thanks Ali, I'll have a look.

I just uploaded a cut down Excel version which focusses on the Date Interval problem

[AliGW]

OK - looking at your CUT workbook, I cannot see where you have MOCKED UP your expected results. Which column, please?

Please add calculated results manually so that we can see what you are after.

[AliGW]

To be clear - please fill in the yellow section MANUALLY to show the results you expect for this sample:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	G	H	I	J
1	HoleID	Date Measured	mBTOC	mBGL
2	H1	10/01/2024	10
3	H2	10/01/2024	20
4	H3	10/01/2024	30
5	H1	10/04/2024	11
6	H2	10/04/2024	22
7	H3	10/04/2024	33

Sheet: Sheet1

[lucaskre8]

Thanks again Ali, yes new version attached, I hope this makes it clearer

[AliGW]

OK - try this:

=LET(f,FILTER($A$2:$C$5,$A$2:$A$5=G2),I2-XLOOKUP(H2,INDEX(f,,3),INDEX(f,,2),"N/A",-1))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[lucaskre8]

Thanks, and thanks for the previous help as well. I'll see if i can work out my other problem myself, before starting a new thread.

[AliGW]

You're welcome!

You'll certainly learn more by trying to work things out, but if you ever need an explanation of a solution given, just ask.