Find/Lookup row using "between" criteria

New to Smartsheets and struggling with being able to search a table to find which rows a field lies "between", and use the higher row

Sheet 1 - Task Tracker that contains hundreds of 'data loading' tasks for a data conversion process with many columns that are populated by team members, relevant columns below

Col1 - Data Load DateTS (text field populated by end user): "7/05/2018 3:01:02 AM"

Col 2 - DateLkUp field (value of col1 turned a unique ID): 20180705030102


Sheet 2 - Export Tracker contains a small number of rows with the exports (backups) taken periodically throughout the data conversion process

Trying to pull the export name of the Export that contains the data that has been loaded based on the DataLkUp field from Sheet1

I've tried using Vlookup with the 'approximate' match-type of true, but it is finding values that are earlier than the DateLpUp field -- using the data above, the VLOOKUP is retrieving data from row 1 - "exp_Jul1.xar" when what I'm trying to get is row 2.

The data was loaded on July 5 at 3AM, which is AFTER Row 1 but before Row 2 July 5 at 12:15 PM - the data loaded isn't in the export logged in Row1 it IS in the export logged in row 2

Formula =IF(DtLkUp3 = " ", " ", VLOOKUP(DtLkUp3, {Tenant Exports Range KG}, 3, true))

I also tried to use

=JOIN(COLLECT({ExportNameLs}, {ExpDt_UID_Ls}, {ExpDt_UID_Ls}<=DtLkUp3,{ExpDt_UID_Ls}>DtLkUp3))

but am either getting a parsing error or invalid operation errors.

I looked at trying to use index & Match, but match doesn't support finding the 'between' row.

I'm stuck, please help!

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!