Contextures

Contextures News 20181113

Excel VLOOKUP Trick

November 13, 2018

Quickly close all files, VLOOKUP trick, 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.

Close All Files

In Excel 2013 and later, each file opens in its own window - a Single Document Interface (SDI). It's a nice feature, but it can take a bit of time to close all the files, when you're done for the day.

Here are 3 ways to quickly close all those open Excel windows.

QAT: Put the old "Close All" and "Exit" commands on the Quick Access Toolbar.

  • Close All and Exit on QAT

Top Right X: Press the Shift key, and click the X at the top right of one of the Excel windows. You will be prompted to save any unsaved files, and then all the windows will close.

  • Shift and click X

Keyboard: Press the Alt key, and tap the F key, to activate the File tab. Then tap the X key, to run the Exit command (even though you can't see an X shortcut in the list).

Go to the Excel Files FAQs page on my website, to see other ways to close all windows, and more tips for working with Excel files.

VLOOKUP Trick

To use the VLOOKUP function, the value that you're looking for has to be in the first column of the lookup range. But what if your lookup table has Scores in the 3rd column, and you want VLOOKUP to find a description in the 2nd column? Which Excel function could help?

lookup table

To get the VLOOKUP to work, combine it with the CHOOSE function. Usually, CHOOSE makes a simple selection from a list of options, such as finding the Fiscal Month, based on calendar month number.

=CHOOSE(MONTH(C6),7,8,9,10,11,12,1,2,3,4,5,6)

But CHOOSE has extra powers -- it can create arrays too. In this example, there is a score in cell C9, and this formula in cell C10:

=VLOOKUP(C9,CHOOSE({1,2},D3:D7,C3:C7),2,TRUE)

The numbers inside the curly brackets - {1,2} - tell Excel to create a 2-column array, with Scores in column 1, and Descriptions in column 2.

Here's what the array looks like, if you evaluate the CHOOSE function in the formula bar.

array for index number

VLOOKUP looks for the score in the first column of that array (D3:D7), and returns the value from the 2nd column (C3:C7), using an approximate match.

Go to my Contextures site, to see more examples for the CHOOSE Function.

Excel Articles

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

Excel Tables - If you use Excel tables, get the free Table Tools addin from Jan Karel Pieterse, to quickly rename or navigate tables. (Level - All)

Charts - Jeff Lenning shows how to create a dynamic chart title with Slicers. (Level - Int)

Power BI - Matt Allington explains why you should be interested in the new Power BI feature, Dataflows, that let you collect and store data. (Level - Int)

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

Tidying Up

I'd heard about the KonMari Method of tidying up, and finally got a copy of the book from Mississauga's online library. While I haven't embraced the whole system of decluttering, I have become a fan of the method for folding clothes. It's easy to do, and you can see everything that's in your drawers. I hope she has some tips on organizing computer files too!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181113ctx.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: November 17, 2018 9:35 AM