Solutions to Three Common Problems when Using VLOOKUP()

This article addresses some common issues you may encounter when you use the VLOOKUP function.

This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.  When using VLOOKUP() we frequently find ourselves facing three common problems: 

  • We need to look up based on more than one column
  • We’re getting an #N/A though the key is valid  
  • We’re confronted with the Zoolander problem

 Problem #1:  We need to look up something from a column, but we have to look up on two keys instead of one.  Let’s say we need to return the State information from the following table.

Produce

Color

State

Apples

Red

Washington

Cherries

Red

Michigan

Bananas

Yellow

Hawaii

Lemons

Yellow

Texas

Grapes

Green

California

Apples

Green

Pennsylvania

Grapes

Purple

New York

We cannot use just the Color column – would “Red” mean Washington?  Or Michigan?  We cannot use just the Produce column – would “Apples” mean Washington? Or Pennsylvania?  We need to look up on both Produce and Color.

Also read Why Do You Need Formulas and Functions?

Solution to Problem #1:  The simplest solution is to create a helper column that combines both keys at once.  To help readability we can insert some type of delimiter between the two fields such as the pipe (|), a comma or a semicolon.  When we combine two or more pieces of text (which are frequently called “text strings” or more simply “strings”) we call this combining process “concatenation”. The concatenation operator in Excel is the ampersand (&) so our helper formula (using the pipe character) would be:

  =A2 & ”|” & B2

 After inserting the helper column, the formula and copying down, we end up with the following table. Note that our composite column still needs to be to the left of the column whose data we will be returning.

Produce

Color

Composite

State

Apples

Red

Apples|Red

Washington

Cherries

Red

Cherries|Red

Michigan

Bananas

Yellow

Bananas|Yellow

Hawaii

Lemons

Yellow

Lemons|Yellow

Texas

Grapes

Green

Grapes|Green

California

Apples

Green

Apples|Green

Pennsylvania

Grapes

Purple

Grapes|Purple

New York

We can create a similar composite column to use inside the VLOOKUP() or we can do the concatenation inside the VLOOKUP() formula.  Examine the formula in the formula bar below to see an example of this.

Go to Problem #2:

Go to Problem #3:

Also read Excel Formulas: 10 Formulas That Helped Me Keep My Job


Courtesy: blogs.office.com


1

2 Responses

Show all responses
  1. Luigi
    August 27, 2016
  2. Luigi
    August 27, 2016

Leave a Reply