# Solutions to Three Common Problems when Using VLOOKUP()

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.

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:

Courtesy: blogs.office.com

#### 2 Responses

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