Contextures

Contextures News 20190514

Excel Lookup Formula

May 14, 2019

Create Excel names, another lookup formula, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Lookup Formula

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.

LOOKUP last entry - text or number

With values in cells B1:B6, I used this formula to get the last entry that's text or a number:

=LOOKUP(2,1/(B1:B6<>""),B1:B6)

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)

LOOKUP TRUE or FALSE

Then, 1 is divided by each result, and returns either a 1 or a #DIV/0! error.

LOOKUP divide 1 by result

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.

Excel Training

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.

Excel Names

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:

  • Copy the number from the formula bar
  • On Excel's Formula tab, click Define Name
  • Type a name, e.g. XL_Max
  • Leave the scope as Workbook
  • Type a comment that explains what the number is
  • Right-click the Refers To box, and click Paste, then click OK

define a name

  • Then, put that name into the formula, to replace the number.

use name in formula

Read more about Excel Names on my Contextures site.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Tulip Festival

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!

weekly photo

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.

Debra Dalgleish
dsdalg@ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 10, 2019 2:31 PM