Contextures

Contextures News 20180619

Excel Double Lookup Formula

June 19, 2018

Quick tip, 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: You'll get the next newsletter on July 3rd -- we're on the summer schedule now.

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

Quick Tip

If you work with tables or lists, there's a built-in data form that you can use to enter, edit or find items. It's not on the Ribbon, so use a shortcut to open it -- press Alt+D and then tap O.

For more features, there's a free Enhanced Data Form on John Walkenbach's site.

Data Form

Using RAND

For a bit of fun with the RAND function, get the Excel team's Soccer Tournament Predictor. It shows all of this year's World Cup matches, and predicts the winners. Even if you're not interested in the sport, the formulas are worth a look (check the hidden columns).

If you're interested in the technical side of the RAND function -- Gizmodo is overly worried about Canada using RAND to sort names for a lottery. I liked the comments more than the article! The RAND algorithm changed in Excel 2010 - there's info on Microsoft's RAND page, and see the description of RAND's algorithm. Also interesting is Rich Pollock's article, Randomness in Excel.

RAND function example

Also, there are more RAND and RANDBETWEEN examples on my Contextures website.

Double Lookup

You can combine Excel's INDEX and MATCH functions, to create a flexible lookup formula. Most of the time, MATCH gets an item's location, and INDEX returns an item from that location, in a different column.

In this Mileage Lookup example, there are city names in a vertical list at the left, and a horizontal list at the top. The distances between cities have been typed into the table, at the point where the city names intersect.

Select two cities from the drop down lists, and this formula finds the distance between them.

=IFERROR(INDEX($H$4:$Q$13, MATCH(B4,$G$4:$G$13,0), MATCH(B5,$H$3:$Q$3,0)),””)

The formula does the lookup in 3 parts:

  • MATCH 1 finds the first city, to get the row
  • MATCH 2 finds the second city, to get the column
  • INDEX returns the mileage from that row and column within the its range.

There are details on my blog, and to get the sample workbook, click here.

mileage lookup

Excel Articles

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

Forecasts - Liam Bastick shows how to revise forecasts efficiently and effectively. This technique is definitely not for beginners - download the file to follow along. (Level - Int/Adv)

Charts - Jon Peltier shows how to make Cycle Plot charts in Excel, to compare data side-by-side. You can skip the "Secondary Axis for Labeling" steps, to make the setup easier. If you have Jon's Chart Utilities (Advanced), use the Cycle Plots command. (Level - Int/Adv)

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

In the Garden

We planted a clump of rhubarb long ago, and I'm not sure what variety it is, but the stalks are green inside, not red. I made stewed rhubarb from this year's spring harvest, with a lot less sugar than this Canadian Living recipe calls for. And 6 cups of rhubarb will not serve 750 people, as their recipe claims!

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: http://www.contextures.com/newsletter/excelnews2018/20180619ctx.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: June 15, 2018 4:06 PM