Hi - Dave here.
Happy Friday!
An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price.
The best way to solve this problem depends on which version of Excel that you use. In Excel 2019 and earlier, the classic solution is to use the MAX function to find the maximum value, then use this value in an INDEX and MATCH formula as the lookup value.
In the current version of Excel, there is a better way. Instead of performing a lookup operation, you can use the SORT function to sort by price in descending order, then use the TAKE function to retrieve the first row as seen below:
[
Click here to read the article and download the workbook]
This new approach is simple and elegant, and it greatly simplifies many complicated formulas. The article explains the new approach based on SORT and TAKE, as well as the traditional approach based on INDEX and MATCH. Don’t forget to download the worksheet and try it out yourself.
Wrong link!
Last week, I mistakenly sent the wrong link for the two-column lookup problem I shared. Sorry if you ended up confused! The correct link is below:
INDEX and MATCH two-column lookup example
Summer emails
I'm going to be traveling a fair bit for the next several weeks, so expect emails to be more irregular. I hope your summer is off to a good start.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
