Blog

Best Excel Trick Ive Seen Lately

Best Excel Trick Ive Seen Lately

Sep 01 2015

I love Excel. There, I said it! And I love *substantial* worksheets that do heavy lifting for you. So it's no surprise that I use a lot of table lookup and summary functions in the spreadsheets that I put together, often to automate things like project reporting, issue management, etc. for internal project use.

So this evening I was working on just such a spreadhseet. I needed to lookup syntax for an old friend, the VLOOKUP function, and in the process of doing that I stumbled on one of the coolest tricks ever! The traditional problem with VLOOKUP is that it really anticipates a simple named value pair sort of lookup table. You know, KEY - VALUE. But what if you have to add a third column, or even if you want to use VLOOKUP with a full-on, multi-column database table? That's not something you've normally been able to do.

But then I ran across this *gem* of an article... see the tip on "Look Up Items With the VLookup Function".

The article illustrates how you can embded a CHOOSE function within the VLOOKUP to accomplish the unthinkable... getting VLOOKUP to pull data from another column! I mean, SWEET, right? OK, only for Excel nerds... but I assure you, Excel nerds like this!

I've borrowed one image from that blog to illustrate, it's the blog image above (please read that full article if you're interested).

Now consider this formula:

“=VLOOKUP(DATE(2013,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)”

The normal VLOOKUP syntax is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

By inserting the CHOOSE() function as the table_array, you're essentially creating a table_array on the fly that is a two columnm, key-value pair. The CHOOSE function tells excel to assign reference "index" numbers to the specified data ranges. So we're assigning the index value "1" to the [Date] column and value "2" to the [Task] column, which are not even next to each other, not to mention out of order (in terms of normal VLOOKUP usage).

So any time you need to do a KEY - VALUE lookup in a database table, you can now use a VLOOKUP function to do it , rather than having to create a series of conditions for a DB lookup or some combination of Index and Match functions.

Many thanks to Ryan Dube for an awesome Excel tip!

Scott is a Software Delivery Program Director for a boutique consulting firm and owner of 11•2 Labs. Ever the entrepreneur, Scott is also co-founder of the DNN open source project and DNN Corp. A published author (DNN for Dummies, Professional DNN 7), Microsoft MVP and avid sea kayaker, Scott is a regular contributor to the Alki Homes blog and responsible for all of dnnHomes technical programs and social marketing.