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.
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:
The MATCH function could find the Region heading in the lookup table:
So, to make the formula flexible, replace the typed "2" with the MATCH formula, then copy across to the other columns:
Go to the VLOOKUP page on my website, to watch a short video that shows how this works, and download the sample workbook.
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.
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)
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?
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: July 21, 2017 9:38 AM