Category Archives: Uncategorized

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.

Bill Jelen: “From 1979 – VisiCalc and LOOKUP”!

You read correctly – 1979. In Bill’s last installment for VLOOKUP WEEK 2012, we go back to 1979…VisiCalc and the whole ’20 Functions’ available in that time! There were no IF statements and there was no VLOOKUP…but there was ‘LOOKUP’. Follow along and see where VLOOKUP began!

QUE: The Basics of The VLOOKUP Function

From QUE Publishing:

“It is day #2 of VLOOKUP Week and who better to give the lay of the VLOOKUP land than Paul McFedries, author of “Formulas and Functions for Excel 2010. In the following post, he breaks it down nice and neat like…” Click Here To Read More…

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 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.

James Chen: Use Consistent Sheet Names

James Chen sends along this guest post. James is a member of the Excel Gurus group at LinkedIn.

More often than not we need to pull info from various worksheets in a workbook. If you use care to consistently name the worksheets, you can combine VLOOKUP with INDIRECT to pull data from different worksheets. This technique is a life and time saver.

Let’s say you have a sales sheet with various customers and you get a new worksheet every month. Be consistent in choosing a naming rule for the worksheets. For example, you could use Sales_Jan_2012, Sales_Feb_2012, Sales_Mar_2012. This is what I like to call uniform naming.

On a summary sheet, you want to lookup sales for customer XYZ from each monthly sheet. Say that you have true Excel dates for Jan 2012, Feb 2012, Mar 2012 in B3:D3. You have the customer name in A4. The structure of each monthly sales sheet is customer in A and sales in B. Try this formula:


How it works: the TEXT function formats the date in the format of Jan_2012. The ampersand is a concatenation operator, so you end up passing Sales_Jan_2012!$A:$B to the INDIRECT function. The function will dynamically look at a different worksheet based on the date in row 3.

This is especially useful when you have multiples upon multiples of uniform source data sheets that you need to pull from.

QUE: Using a Helper Row with VLOOKUP

The QUE blog checks in with a VLOOKUP-themed post about using a helper row with VLOOKUP. This technique makes it easier when copying a VLOOKUP formula across several columns.

By the way, my thanks to QUE for donating five door prizes for VLOOKUP Week. If you haven’t entered the daily door prize drawing, visit the Door Prizes link across the top navigation bar.

Read the complete article here.

Rob Collie from PowerPivotPro: Does PowerPivot make VLOOKUP Obsolete?

The first time that I saw PowerPivot create a pivot table from two worksheets WITHOUT doing any VLOOKUPs, I realized that a lot of people who thought VLOOKUP was scary would now be able to do some very powerful analyses. My favorite blog on PowerPivot is run by former Excel Project Manager Rob Collie. This morning, at 12:04AM, Rob checked in with a blog post that offers two amazing techniques:

  1. Joining two tables in PowerPivot using the Create Relationship dialog box
  2. Once you’ve joined two tables, using the DAX function =RELATED in the PowerPivot grid to replace VLOOKUP

Read Rob’s post, VLOOKUP Week – Who Needs VLOOKUPs Anymore. article: Handle Growing Lookup Tables with a Two-Key Combo

CFO Magazine offers a solution for growing pivot tables.

…If you need to add new rows to the lookup table, the obvious place is to type the new rows in X8:Z9. However, these rows are outside of the lookup table, so you must re-enter the existing VLOOKUP formulas…. Read complete article at