Google Sheets – Get Cell Value Based on Another Cell

google sheets

I'm looking for help on the following :

I need the word Frisbee to be put in the green cell

Any idea ?

The value researched will always be text, and depending on a date.

Edit :
Just made the spreadsheet public and open to comments, sorry.

I have a column C with items acquired and a column B with the date of the acquisition. It's not sorted by date, so I first have to acquire the most recent date (with =TO_DATE(MAX(ARRAYFORMULA(B2:B9))) ), then I need to get the content of the cell that is corresponding to the date (on the same line, separated horizontally by one or more cell).

Best Answer

You can use the following formula to get the "frisbee".

Formula

=QUERY(B3:C8; "SELECT C ORDER BY B DESC LIMIT 1")

Explained

The QUERY will take both columns as dataset, but it will only show column C. Furthermore, it will sort column B descendingly (highest first) and the limit option will limit the result dataset to one row.

In this case, there's no need to calculate the highest date first.

Example

I've copied your file and added my solution: FRISBEE

From the question

Is it possible for creating the subset (filtered by Month) in a way that if we were to make changes in that subset view, those changes would cascade back to the Master. Would QUERY work? Or do I have to resort to a script. Or it is not possible.

Short answer

Google Sheets doesn't have a built-in feature that syncs two spreadsheets so you have to use something that extends it.

Explanation

Built-in functions like QUERY are not able to make make changes to another range. They could only return a value or an array of values. The same apply to custom functions.

Below is included a very simple Google Apps Script code example that exports the value of an edited cell to a cell of another spreadsheet. That code could be added to two spreadsheets to export the edited cells values to each other in order to have a "two way sync" between those spreadsheets.

It's purpose is to show that it's possible to sync two spreadsheets by using Google Apps Script. In order to make it work to sync a master sheet with filtered sheet to logic to find which rows are the match in the other file should be added. Usually this is done using an unique ID.

Broad instructions

Copy the code to script projects bounded to the each spreadsheet and update the targetID and targetSheetName variable definition code lines (lines 1 and 2).
Add a On Edit installable triggers to each script project.

Code

var targetID = 'spreadsheet-id';
var targetSheetName = 'Sheet1';

function onEdit(e) {
  // Get the event object properties
  var range = e.range;
  var value = e.value;
  //Get the cell position 
  var row = range.getRowIndex();
  var column = range.getColumnIndex();
  exportValue(row,column,value)
}

function exportValue(row,column,value) {
  var ss = SpreadsheetApp.openById(targetID);
  var s = ss.getSheetByName(targetSheetName); 
  var target = s.getRange(row, column);
  target.setValue(value);
}

Limit the changes to be synced

In order to limit the changes to be synced add rules to the code. The JavaScript comparators ==, ===, !=, !==, >, <, >=, <=, and the logical operators &&, ||, ! with if, switch among other commands could be used to create simple of complex rules.

References

Google-sheets – Select last entry from spreedsheet

=QUERY(A2:C5; "limit 1 offset "&(COUNT(A2:C5)-1))