From
exceljet.net
Email Address
contact@exceljet.net
Sent On
Fri, Aug 9, 2024 01:03 PM
Email Preheader Text
Hi - Dave here. Happy Friday! One of the most interesting aspects of the new functions in Excel is h
HTML
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) [Listing the coordinates of a matching value in a table]( [[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](. [View this email in a browser]( [Exceljet Logo]( Exceljet
P.O. Box 4804
Salt Lake City, UT 84110 Copyright © 2024 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below. [Unsubscribe](
EDM Keywords (55)
years
would
worksheet
workbook
week
wanted
value
use
unsubscribe
try
tools
tocol
time
think
test
take
table
subscribed
structure
solved
solve
sign
show
see
return
results
result
recently
received
realized
read
playing
nutshell
note
newsletter
need
maintain
looked
location
list
link
learn
know
important
however
function
fridays
free
formula
flatten
excel
example
email
coordinates
challenge
catch