Contextures

Contextures News 20181023

Excel LOOKUP Function

October 23, 2018

LOOKUP function tricks, product updates, 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 Function

Last week, you found a formula to calculate the shift number, based on a start time. My solution used the VLOOKUP function, and here's another way to solve it - with the LOOKUP function. (Thanks UniMord!)

=LOOKUP(A4,$D$4:$E$7)

The formula finds an approximate match for the start time in the first column of the lookup table (D), and returns a value from that row, in the last column of the lookup table (E).

Get Shift Number

Go to my Contextures website, to see more LOOKUP examples, and download the workbook. For example, find a numeric score in column D (sorted smallest to largest), and return the letter grade from column C. You can't do that with VLOOKUP, where the lookup column is always at the left:

=LOOKUP(C10,D4:D8,C4:C8)

get letter grade from number

Product Updates

If you've bought any of my Excel products, a couple have been updated recently. Use your original download link to get the latest version. Or, if you can't find your link, email me, and I'll send you a new link.

Pivot Power Premium (PPP) - Minor update, with a couple of improvements for Data Model pivot tables, and in the "Pivot Prefs" form. See PPP product details. (Ver 5.02)

Data Entry Popup (DVMSP) - Minor update, to fix a problem when popup is set for "specific cells only". See DVMSP product details. (Ver 6.10)

Excel Articles

Here are a few Excel articles that you might find useful or interesting.

Data - Do you follow all the guidelines in this article on data organization in spreadsheets, by Karl Broman and Kara Woo? The key points are in the Abstract, so read that, if you don't have time for the full article. (Level - All)

Errors - As a tribute to Spreadsheet Day, Jon Peltier wrote a tongue-in-cheek article about Excel error messages. How many of these gems have you seen? See more, in my Spreadsheet Day wrap up post. (Level - All)

Tips - In the Actuary community on Reddit, there's a discussion with Excel tips. I didn't notice any offensive language, but it's Reddit, so enter at your own risk! (Level - All)

Reminder - Registration for Jon Acampora's popular VBA Pro Course is closing this Thursday -- Oct. 25, 2018.

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

The Colour Purple

This summer, we planted a package of seeds for a Butterfly Garden. Not much happened over the summer, when the butterflies were visiting our garden, but all kinds of flowers are blooming now.

These little purple flowers are the latest arrivals, and I don't know what they are. The seed packet didn't have any details on what varieties were included. It's fun to be surprised though, so maybe we'll try again next year, and get the planting done earlier!

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181023ctx.html
I'll also post any article updates or corrections there.

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: October 20, 2018 2:22 PM