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
-
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?
-
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.
Answers
-
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?
-
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
-
Are you always going to be referencing DTLkUp3?
-
DTLkUp column? Yes. But the formula will be on each row on sheet 1 so DtLkUp3 on row 3 DtLkUp4 on row 4 etc.
-
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.
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!