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.

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.

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