Looking to return a row cell (date) from an INDEX look up of 2nd last entry in a ROW
Title probably not a good description but can anyone help please?
I have rows of players with columns of dates they could possibly have scored a goal in. Column A returns the number of goals scored the last time they did but I am looking to match that entry with the date they last scored on either in in Column B
Sample sheet link below. Looking for Column B to be automated to return the entry manually entered in B3,4 examples.
https://docs.google.com/spreadsheets/d/1yYJf3iQU43uWc2cEjJhVm1TQ1NB_VbYrETgLX6ka1H4/edit?usp=sharing
Thanks in advance, possibly a simple solution, but I cannot see it.
Solution
You may try:
=byrow(E3:U,lambda(Σ,if(counta(Σ)=0,,choosecols(filter(E2:U2,Σ<>""),-1))))
If the dates are ever-expanding & the formula needs to pick 2nd last entry(skip the Grand Total value)
=byrow(indirect("E3:"&rows(A:A)),lambda(Σ,if(counta(Σ)=0,,choosecols(filter(E2:2,Σ<>""),-2))))
- Google script to dynamically addressing a cell in google sheet
- Is there any way to do a bitwise shift right on a very large number in Google Sheets?
- Auto numbering rows in GoogleSheet
- Error message while automating data entry from web app contact form to google sheet
- How do I send email to user by appending my domain name to the user name in Google Apps Script?
- How to split comma-separated values across multiple rows dynamically?
- Replace multiple strings in Google Spreadsheet with script
- How to count the amount of times a substring appears in a range of cells?
- Query with multiple data types
- Count If Range Matches Lookup in another tab
- Google Sheets Query where data in either of two columns matches the value in a cell populated by a multi-select dropdown
- Trying to combine words and math in a single cell in google sheets
- Counting the previous sample over multiple conditions throughout the range
- How to randomly select by type probability all items from a google sheet column range
- Sort result of function by RHS column
- Google Sheets Query - using another query to filter dates
- Skip formula if sheet does not exist in Google Sheets
- How to get data from public google sheet and more than 100 rows
- In Google sheets how can I Script the manipulation of the default Zoom?
- Google Sheets IMPORTDATA for xlsx files
- conditional formatting to highlight the highest value in a row compared to all the columns before it
- Google sheets summation formula, iterating until a new header on column A
- Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block
- How to scroll to bottom of a google spreadsheet using selenium
- Calculate average days between dates continuously updating
- Conditional formatting in Google Sheets is recognizing some cell values that equal 0, not others
- Model / Year formula fails when a sequence starts stops then starts again
- Google Sheets: Sumif formula
- Randomize cells in Google Sheets
- Google script for move rows depending on checkbox - Google sheet