Contextures

Contextures News

Make VLOOKUP Flexible

April 4, 2017

Make a VLOOKUP formula flexible, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Make VLOOKUP Flexible

Instead of typing column numbers into a VLOOKUP formula, use the MATCH function to calculate what the column number is. In the screen shot below, we need to pull 3 details from a lookup table, based on an Order ID. With a simple VLOOKUP, each detail would need a separate formula, with its unique column number. For example, here's the formula to get the Region detail, from column 2 in the lookup table:

=VLOOKUP($B6,tblOrdersALL,2,0)

The MATCH function could find the Region heading in the lookup table:

=MATCH(C5,Orders_ALL!$A$1:$D$1,0)

So, to make the formula flexible, replace the typed "2" with the MATCH formula, then copy across to the other columns:

=VLOOKUP($B6,tblOrdersALL,MATCH(C5,Orders_ALL!$A$1:$D$1,0),0)

Go to the VLOOKUP page on my website, to watch a short video that shows how this works, and download the sample workbook.

MATCH function finds heading position

Data Entry Search Popup

I've added a new product on my website -- Data Entry Search Popup. It's similar to the original DVMSP kit, but designed for list that have lots of items, or lengthy items. Use this kit to add the popup lists to your workbook. Then, share that file with your co-workers -- they don't need a copy of the kit to use your completed files.

  • Type a few letters in the Search box, and the list is filtered to show only those items.
  • A text box shows the full text for long items.
  • Add the items to the worksheet in the order in which you selected them, or in A-Z order

To see how it works, click this link to get the free demo file. Go to the DESP page for more details, or to buy a copy of the kit.

Excel Articles

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

Charts - Ann K. Emery shows the steps for planning and building an Excel chart, from the sketching phase, to the completed chart. What works, and what doesn't? There's a video that shows the process too. (Level - All)

Ofice Dev - If you're looking for a new adventure in Excel, the latest Office 365 Developer Podcast covers the basics of getting started with Office 365 Development. The "Getting Started" section starts at about the 22 minute mark, and the page has many links to resource material that you can use. My favourite link -- Modern JavaScript for Ancient Web Developers. (Level - Intermediate/Advanced)

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

This Party Rocked

Last weekend, my granddaughter (centre, pink dress) had a birthday party, and her parents wisely decided to have it at the local YMCA. If kids are going to climb the walls, it might as well be someone else's walls! Back in the old days, we played Pin the Tail on the Donkey, or a rousing game of tag, and ate cake -- nothing as exciting as costumes, crowns, and rock climbing.

And is it just me, or does this kind of look like an Excel chart made from children?

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

Debra
ddalgleish @ contextures.com

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

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:38 AM