Excel Double Lookup Formula
June 19, 2018
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!
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 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.
Also, there are more RAND and RANDBETWEEN examples on my Contextures website.
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:
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)
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!
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/20180619ctx.html
I'll also post any article updates or corrections there.
dsdalg @ gmail.com
Last updated: August 21, 2020 12:54 PM