Author Archives: Bill Jelen

About Bill Jelen

Author of 41 books about Microsoft Excel.

Looking Up Multiple Criteria with INDEX and SUMPRODUCT

JP Pinto won the fan-favorite Great White Shark award for his previous post about Cascading Validation Lists. The folks at the Office Online blog asked JP to do a new guest post for the Excel blog and JP’s post on using INDEX and SUMPRODUCT to do a two-criteria lookup is now available here. This looks like a cool technique. JP starts out by using SUMPRODUCT to return a numeric value from a column. But if you need to return a text value, the INDEX with SUMPRODUCT and ROW solves the problem.


Charles Williams: Doing a wildcard vlookup!

I had never tried this, but you can use a wildcard in your VLOOKUP. Charles Williams shows an example. Click here for complete article.

Find the first name containing "n". Charles will show you how.


Tom Urtis: Lookup with 2 or more criteria

Today’s post from Tom Urtis involves finding the record that matches two criteria, such as Chevrolet in column A and 4-Door in column B. The solution involves INDEX, MATCH entered as an array formula, so this is ranked pretty high on the Excel spicy scale. Read the complete article here.


Charley Kyd from ExcelUser.com never uses VLOOKUP

When I announced VLOOKUP Week, Charley Kyd was one of the first to respond with something along the lines of “I never use VLOOKUP and I will be happy to tell you why!”  Although Charley was one of the first to respond, I figured I should hold this post for a few days so I did not burst the bubble of anyone new to VLOOKUP. To read why Charley likes INDEX/MATCH better than VLOOKUP, check out his complete post.


Debra Dalgleish Uses VLOOKUP to Track Shark Attacks

Excel MVP Debra Dalgleish checks in on her Contextures blog with a beginning VLOOKUP example that actually tracks the number of shark attacks. (Nice use of theme, Debra!) Debra’s post includes the FLOOR function, Data Validation, and links to her videos on VLOOKUP. Read the complete article here.

The eBook “Master 30 Excel Functions in 30 Days” by Debra Dalgleish is Available Here. You may find Debra’s work at Contextures.com and via Twitter @ddalgleish.


I Would be Screwed at my Job without VLOOKUP! (Ode to VLOOKUP)

I formerly worked as the Manager of Dealing with Bad Data.

OK – maybe that wasn’t my real job title, but it is what I did day after day. I was the guy who knew how to use a bad mainframe tool to extract imperfect data sets and then coax useful information out of them in a spreadsheet. I used VLOOKUP constantly to join this data with that data. Why didn’t we just get all the data in one download? Because this was 1989 and it just wasn’t possible with the $100K 4GL tool that my company purchased. Of course, using a $299 spreadsheet and VLOOKUP, everything was possible.

I held this position for 10 years. They gave me other job titles and promotions, but, I was still doing the same thing: dealing with bad data. I overheard someone ask the I.T. manager if we ran our company on Oracle or SAP. He had to admit that we pretty much ran the company on Excel. He probably didn’t mean that as a compliment, but I was proud of his statement.

When I announced VLOOKUP Week on Facebook, one of the comments was from Erica:

“I use vlookup and pivot tables countless times a day, I would be screwed at my job w/out it.”

Erica and thousands of others and I could not live without VLOOKUP. If you are one of those thousands, I invite you to comment on this post with a few lines about how you use VLOOKUP. Feel free to be creative. Be passionate. Write a little love note to VLOOKUP. You don’t need to drop into verse or rock out a haiku, but let the world know that “you have VLOOKUPs and you know how to use them”.

For our door prize today, we have a VLOOKUP Week t-shirt and another copy of Paul McFedries’  Formulas and Functions Excel 2010. To be entered in the drawing, post a comment to this thread with your Ode to VLOOKUP. Enter by 6AM on Wednesday to be eligible.


Richard Schollar: VLOOKUP Left!

Richard Schollar provides this guest post. Richard is an Excel MVP from the U.K.  When I saw the title of Richard’s post, I figured he was going to use INDEX/MATCH, but Richard really uses VLOOKUP!

How do you use a VLOOKUP if your table data holds the lookup value in a column to the right of the data column you want to return?

We’ve all had this problem – you want to use VLOOKUP but your data is ‘round the wrong way’:

You have the ID, you want to return the Description.  A normal VLOOKUP won’t work as you can’t use a negative column:

One alternative is to use INDEX/MATCH e.g.:

But wouldn’t it be cool to be able to use VLOOKUP?  But we can’t though, can we?  Sure we can, if we make use of the CHOOSE function:

That formula in G2 is:

That formula is =VLOOKUP(F2,CHOOSE({1,2},$C$2:$C$7,$A$2:$A$7),2,False)

How it works: CHOOSE returns an array of two ‘columns’ in the right order, based on the order in which you pass the columns into the CHOOSE function.  This creates an array that is in the correct Left-to-Right orientation for VLOOKUP to work. Here is the Evaluate Formula dialog after evaluating the CHOOSE function: