Find a Matching Value in Date Range and Return Value

Find a matching value in date range and return value

 SELECT 
          t.ID
        , t.Date
        , t.ArtNo
        , t.SellPrice
        , pr.Price

FROM TRANS t
OUTER APPLY ( SELECT TOP (1) p.Price
              FROM Price p 
              WHERE p.ArtNo = t.ArtNo 
                AND p.Date <=t.Date
              ORDER BY p.Date DESC
            ) pr(Price) 

Return cell's value based on its date range in Excel

If the given Ranges both start at A1 and end at D3 then the following works in Sheet1!C2:
=INDEX(Sheet2!D:D,MATCH(1,(B2>Sheet2!B:B)*(B2<Sheet2!C:C)*(A2=Sheet2!A:A),0))

This is an array formula to be entered with ctrl + shift + enter

It Indexes sheet2 column D and searches for the first match where all mentioned condition are true (=1). Each condition produces 1 or 0 for each cell in the range and multiplies it by the result of the cell from the next range in the same row. If either of the conditions is false it multiplies by 0 resulting in 0.
If all conditions are true it will result in 1 (111).
The overall produces an array of {0,0,1,0,...} and the match function returns the N'th occurance of the first 1, which is equal to the row number of the conditions being true.

INDEX MATCH to return multiple results based on date range and name criteria

If you have Microsoft-365 then use FILTER() function like-

=FILTER(A3:D10,(C3:C10=H2)*(B3:B10>=H3)*(B3:B10<=H4),"No Record Found")

For older version of excel you can use AGGREGATE() function. See second screenshot.

=IFERROR(INDEX($A$3:$D$10,AGGREGATE(15,6,(ROW($3:$10)-ROW($2:$2))/(($C$3:$C$10=$H$2)*($B$3:$B$10>=$H$3)*($B$3:$B$10<=$H$4)),ROW(1:1)),COLUMN(A$1)),"")

Screenshot of FILTER function.

Screenshot of Aggregate function.

How to extract a list of indices based on a date range and Category match in Excel? [Ver: Standard 2019]

Use AGGREGATE:

=AGGREGATE(15,7,(ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1)/(($A$2:$A$7>=$B$2)*($A$2:$A$7<=$C$2)*($D$2:$D$7=$E$2)),ROW($ZZ1))

Index Match with updated value by date range

Formula is:

=SUMIFS($C$2:$C$3;$A$2:$A$3;G2;$B$2:$B$3;H2;$D$2:$D$3;"<="&I2;$E$2:$E$3;">="&I2)

The tricky part here is the date criteria: your purchase date must be greater or equal to From Date and must be also lower or equal than To Date. Following that logic can help you out to track the price on a exact time period, country and client.

Google sheets - Search for a date in a range and return value underneath?

I believe your goal is as follows.

• In your showing sample Spreadsheet, you want to know whether the checkbox is checked or unchecked.
• From let's say that today i want to search for the date 2022-03-30 (the marked number on the picture below) and retrieve the value underneath to see if the checkbox is ticked or not,, in this case, you want to check whether the checkbox of "T12" is checked.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet name, and save the script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const oneMonth = { cols: 7, rows: 14 }; // Number of columns and rows of 1 month.
  const calendar = { cols: 4, rows: 3 }; // Order of months in your calendar.
  const spaceCol = 1; // I found an empty column between each month.
  const today = new Date(); // This is today date.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const months = [...Array(calendar.rows)].flatMap((_, i) => [...Array(calendar.cols)].map((_, j) => sheet.getRange(i * oneMonth.rows + 1, j * oneMonth.cols + 1 + j * spaceCol, oneMonth.rows, oneMonth.cols)));
  const res = months[today.getMonth()].createTextFinder(today.getDate()).findNext().offset(1, 0).isChecked();
  console.log(res) // You can see "true" or "false" in the log.
}

• When this script is run, in the case of today (2022-07-05), the checkbox of "S20" is retrieved.
• In order to search the day in a month, I used TextFinder.

Note:

• This sample script is for your provided Spreadsheet. So, when you change your Spreadsheet, this script might not be able to be used. Please be careful about this.

Reference:

• Class TextFinder

Get column index matching the date value in excel

You have to use index/match to get the value in column D corresponding to the match position in column E:

=IFERROR(INDEX(D$3:D$46,MATCH(E3,$B$3:$B$46,0)),"No")

However, according to this Iferror wasn't available until Excel 2007 so you'd have to use Iserror or Isna:

=IF(ISNA(MATCH(D3,$B$3:$B$46,0)),"No",INDEX(C$3:C$46,MATCH(D3,$B$3:$B$46,0)))

For the date ranges

If you decide to use Vlookup:

=VLOOKUP(D3,A$3:C$46,3,TRUE)

Or the two versions of Lookup:

=LOOKUP(D3,A$3:C$46)

=LOOKUP(D3,A$3:A$46,C$3:C$46)

all give the same results.

Note that I am doing the lookup on column A and it doesn't give the same answer as an exact lookup on column B. This is because it's not clear whether 10/4/65 (for example) should produce .25 or .5 because both are possible as 10/4/65 is the end of one range and the start of another and I have chosen to go for the second one.

---

Extra challenge

Would it be fairly easy to do this without doing a lookup and just using Excel 2003 functions?

Yes. If A1 contains the baseline date (maybe a date of birth) 4/4/65, it would look like this:

=YEAR(D3)-YEAR(A$1)+INT((MONTH(D3)-MONTH(A$1)-(DAY(D3)<DAY(A$1)))/3)/4

How to return values only within a specific date range?

After having gathered the data into a dataframe you can convert the column containing the dates to datetime and then through comparison operators mantain just the desidered data.

For example, assuming this is your data:

data = {'date': ['02/02/2022 10:23:23', '09/23/2021 10:23:23', '02/01/2021 10:23:23', '12/15/2021 10:23:23'], 'random': [324, 231, 213, 123]}
df = pd.DataFrame(data)

# convert date column to datetime
df['date'] = pd.to_datetime(df['date'], format="%m/%d/%Y %H:%M:%S")

# select "threshold" date, two months before current one
current_date = datetime.now()
last_date = current_date - relativedelta(months=2)

# select data published after last_date
df[df['date'] > last_date]

If we consider the date of today we will have this result.

Before:

                   date  random
0   02/02/2022 10:23:23     324
1   09/23/2021 10:23:23     231
2   02/01/2021 10:23:23     213
3   12/15/2021 10:23:23     123

After:

                   date  random
0   2022-02-02 10:23:23     324
3   2021-12-15 10:23:23     123

---

Related Topics