arrayssortinggoogle-sheetsgoogle-sheets-formulavlookup
How to return highest value based on two columns in a range
I’m trying to automate a spreadsheet to return the highest value out of a range based on a few criteria.
I want the highest “bid” for each “player” out of range of multiple players and bids. I also need it first to find the highest “year” and then take the highest value from that.
Here is a sample spreadsheet of what I’m talking about.
For example if I want the highest bid for “Joe Mixon” in the range B15:G40 I want to return the Owner, highest year and highest bid returned in cells E3:G3
Hopefully this makes sense! And any advice would be amazing!
Thanks!
Solution
use:
=INDEX(IFNA(VLOOKUP(B3:B12&C3:C12&D3:D12,
SORT({B16:B&C16:C&D16:D, E16:G}, 3, 0, 4, 0, 2, 0), {2, 3, 4}, 0)))
where
3, 0, 4, 0, 2, 0
Year, descending, Bid Amount, descending, Owner, descending
eg. priority is:
Year > Bid Amount > Owner
- Why is the spread operator needed for Math.max()?
- How to access multidimensional data from a json file?
- How to access multidimensional data with loops
- Difference between np.nan and np.NaN
- JS: Filter array of objects by max value per category
- Bash array with spaces in elements
- How to display a multidimensional array
- How to join / combine two arrays to concatenate into one array?
- How do I find a partial match from a data table to an individual cell?
- Parsing a string representation of nested arrays into an Array
- Pipe complete array-objects instead of array items one at a time?
- In Rust, is there any reason to use a `const` array vs array reference?
- How to set a Rust array length dynamically?
- Swift async Loop in Task
- How to print a bash array on the same line
- Looped process only shows the first result per iteration
- How to access a column value in a 2d array?
- Format 2d array data into a string of hyphenated consecutive day ranges based on a shared time range
- Create an array with same element repeated multiple times
- Difference between List and Array types in Kotlin
- Python list with NumPy arrays as elements
- SwiftUI Mapkit: How to reference values from an array for markers?
- How do I Merge two Arrays in VBA?
- Sum of corresponding values from different arrays of the same size with Python
- 2D array in YAML
- Dynamic multidimensional array in twig
- How to append something to an array?
- How does PHP determine the key to use if I declare array elements without offering explicit keys?
- searching an unordered integer array
- PHP JSON (decode)