The best way to solve this problem depends on which version of Excel you use.
In the current version of Excel, there is a better way.
This new approach is simple and elegant, and it greatly simplifies many complicated formulas.
Dont forget to download the worksheet and try it out yourself.
The goal
The goal is to retrieve information related to the maximum price in the worksheet shown.
If the data changes, the formula should automatically recalculate and display updated information.
For convenience, all property information is in anExcel Tablenameddata.
TAKE function
The TAKE function in Excel allows you to return a subset of a given array.
The size of the array returned is determined by separaterowsandcolumnsarguments.
When negative numbers are provided, TAKE will get values from the end of the array.
In the example shown, we want to get all information related to the most expensive property.
For more details, seeHow to use the FILTER functionandHow to use the LARGE function.
In case of duplicates (i.e.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
MAX Function
The Excel MAX function returns the largest numeric value in the data provided.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
MATCH supports approximate and exact matching, andwildcards(* ?)
you might use INDEX to retrieve individual values, or entire rows and columns.
