Check if Cell Contains Month and Compare to Date
I have a dataset that contains a log of tests passed and failed. The tests are in YYYY MONTHNAME format. Look at the screenshot below as an example
I have specific dates for these tests on another tab named Test Date Mappings that I need to compare to columns A and B.
View sample sheet
What I am trying to achieve is columns F and G. On column F, if a person has a test in column C that falls between the start (column B) and end date (column C) then they have a "Yes" in column E. Same with column G using the tests failed column (E).
If a person does not have a start date then the cell should be left blank.
Edit: Updating image and post to reflect a scenario I did not think of initially.
Solution
Here's one approach you may test out:
=map(C2:index(C:C,match(,0/(C:C<>""))),lambda(Σ,if(or(map(iferror(split(Σ,", ",)),lambda(Λ,
index(isbetween(xlookup(Λ,--'Test Date Mappings'!A:A,'Test Date Mappings'!B:B,),indirect("A"&row(Σ)),indirect("B"&row(Σ))))))),"Yes","No")))
- the formula as of now checks if atleast one pass test is within the given start/end dates
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets
- Failed to submit form responses from a spreadsheet
- Maps.newDirectionFinder().SetArrive & SetDepart (Google Maps API) in GSheets App Script aren't impacting returned driving times w/ tested scripts
- Check if range is empty and copy data from Range
- How to convert duration of MM:SS to seconds in Google Sheets?
- Conditional formatting comparing cell to cell values in a range
- showModalDialog in SpreadsheetApp Won't Close
- Google Sheets Create Template