Get highest occurring row number of key?
I'm making a google sheet to calculate ratings between teams that play against each other.
I know I can use the match function to get the index in a column from a key. I made a formula To find the most recent occurring row number of a match, but it looks like it doesn't necessarily return the latest row. How can I find the most recent (highest number) row where a key value occurs?
The googlesheet below shows my attempt. Screenshot added for convenience.
https://docs.google.com/spreadsheets/d/12TIYCNYvpmlsKpbcexKtTqE9KypPmOUx8hIdy_0WF2Q/edit?gid=0#gid=0
Solution
You may use xmatch search_mode -1 (-1 is to search from the last entry to the first)
=ifna(xmatch(B19,$B$1:B18,,-1))
- Get highest occurring row number of key?
- If checkbox checked set values
- google sheet merge two column into one column with prefix words
- Clarification on using the forEach() function
- Why is this ARRAYFORMULA + VLOOKUP() + REGEXEXTRACT() with multiple conditions not working?
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- Add title row with ARRAYFORMULA in Google Sheets
- Rank based on which column is being sorted by in a Table
- Auto border to a Dynamic Data
- How can I determine if a respondent's email address is unique in a list?
- Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY?
- Google Sheets - returning Nth result with multiple matching criteria
- Can a label in a Google Sheets Query be dynamic; can it reference a variable?
- Sum a number of range values; process and add to array
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Why does my Google Service Account not see files shared with it, but can see files created with it?
- Google Sheets macro to copy a line of data to next empty row on another sheet
- Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first column's title
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Split cell with dates and find matching dates on headers
- How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets
- How do I limit a Google Finance formula so it only looks at the first 20 rows?