Find/Lookup row using "between" criteria

Kimberly Gammon

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!

Paul Newcome

Are you able to provide screenshots of both sheets with sensitive/confidential date removed, blocked, and/or replaced with mock data that shows exactly what you are trying to accomplish?

Paul Newcome

Let's try this...

Use the JOIN/COLLECT you have in your screenshot, but remove the second range and insert a comma between the third range and the criteria.

Paul Newcome

Are you able to provide screenshots of both sheets with sensitive/confidential date removed, blocked, and/or replaced with mock data that shows exactly what you are trying to accomplish?

Kimberly Gammon

Sheet 1 - the last column was hand-populated with what the Export Name needs to be...

The Join Collect formula above was just an initial attempt at getting something back from sheet 2 using collect

Sheet 2 which is filled out in ascending order

Looking to find the rows in Sheet 2 where the DtLlUp from Sheet 1 falls between two ExpDt_UID's in sheet two and gets the Export Name value from the row in sheet 2 where the DtLkUp is greater than or equal to ExpDtUID AND less than ExpDtUID

Sheet 1: DtLkUP3 = 20180705030102 this falls between the row 1 and row2 ExpDt_UIDs from Sheet 2

Vlookup's approximate match parm results in finding row 1 in Sheet2 but the ExpDt_IUD value in Row 1 is less than DtLkUp3, I need the reverse where it finds the first row where the DTLkUp3 is less than the ExpDt_UID

Paul Newcome

Are you always going to be referencing DTLkUp3?

Kimberly Gammon

DTLkUp column? Yes. But the formula will be on each row on sheet 1 so DtLkUp3 on row 3 DtLkUp4 on row 4 etc.

Paul Newcome

Let's try this...

Use the JOIN/COLLECT you have in your screenshot, but remove the second range and insert a comma between the third range and the criteria.

Kimberly Gammon

That solved the incorrect argument set problem and I'm getting results using

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

It's a list of all exports that the data would reside in (exp_Jul5 exp_Jul11) - which is way better than the wrong export!

I then tried to get it down to just one entry using two criteria, but get NO results (no error, but no results)

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

Paul Newcome

That's because you are saying that the number has to be BOTH greater than DtLkUp3 AND less than DtLkUp3 at the same time. That's not going to happen, so the COLLECT function pulls no data for the JOIN function to concatenate.