Excel Lookup Formula
May 14, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
In last week's newsletter, I used the LOOKUP function to get the last number from a column. After seeing that example, someone asked me what formula would return the last entry in a column, for text or numbers. Here's the sample data, and the result in cell D2 should be "yes".
What lookup formula would you use? My solution is below the screen shot.
With values in cells B1:B6, I used this formula to get the last entry that's text or a number:
Here's how it works -- The formula tests each entry to see if it's "not equal to" an empty string (<>""), and returns TRUE (1) or FALSE (0)
Then, 1 is divided by each result, and returns either a 1 or a #DIV/0! error.
The lookup value is 2, which won't be found, because the highest number is 1.
Since it can't find a 2, the formula finds the position of the last number 1, and returns the value ("yes") at that location.
Dashboards: Mynda Treacy shows how to make impressive interactive dashboards, using built-in Excel features, in her brand-new one-hour free webinar. Mynda offers full dashboard courses too - get 20% off if you register by Thu, May 16th. Click here for Excel Dashboard Course or Power BI Course info.
Australia: Join Microsoft MVPs for Excel Summit South 2019, this July and August, in Australia. There are 2-day events in Brisbane, Sydney, Melbourne and Perth, featuring Microsoft MVPs and Microsoft employees.
In last week's LOOKUP formula, there was a strange-looking number: 9.99999999999999E+307. It's the largest number you can type into an Excel cell.
Instead of putting that confusing number into the formula, you could define a name, using that value. Then, put the "friendly" name into the formula, so you don't scare your co-workers.
To create the name:
Read more about Excel Names on my Contextures site.
Here are a couple of Excel articles that you might find useful or interesting.
Data Viz - Alli Torban shares great tips for giving and receiving critiques of data visualizations. Use these tips when a co-worker shows you their latest Excel "masterpiece". (Level - All)
Troubleshooting - This Microsoft article shows how to troubleshoot errors when you're trying to save an Excel file. I hope you never need it, but save the link, just in case! (Level - All)
If you're in the Ottawa area this week, be sure to visit the Canadian Tulip Festival. It runs until Monday, May 20th - our Victoria Day holiday. The festival was founded as "a celebration and a remembrance of the Dutch gift of Tulips to Canada’s Capital", after WWII. My parents took me to the festival, long ago, and I think people dress a bit more casually now!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190514ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: May 10, 2019 2:31 PM