Hi - Dave here.
Happy Friday!
One of the most interesting aspects of the new functions in Excel is how they can radically change the way certain problems are solved.
For example, over the years I've wanted a formula that can easily report the location(s) of a value in a table. This seems like it would be a simple problem in Excel, which is all about cell addresses, but it's not in older versions of Excel.
However, I looked at this challenge again recently and realized that Excel now has the tools to solve it in a simple, elegant way. You can see the result in the worksheet below, where the formula in cell N8 is:
=TOCOL(IF(C5:L16=N5,C4:L4&B5:B16,NA()),2)
[
Download the workbook and read the full explanation]
In a nutshell, we use the IF function to test each value in the table against a target value in cell N5, and return the coordinates of matching cells. Then we use the TOCOL function to flatten the results into a single column. I know that most of you have not yet even heard of TOCOL, let alone used it. However, I think it's important to show how these new functions can simplify hard problems in Excel. The catch is that you must invest some time in playing with the new functions to learn how they work. Download the worksheet and try it out yourself.
Note: This formula requires Excel 365 for now.
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
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at one useful Excel formula. You can sign up on our home page.