Solutions to Three Common Problems when Using VLOOKUP()

Today’s author is Greg Truby, an Excel MVP, who 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.

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.

Problem #2: We know our data matches, but VLOOKUP() is returning #N/A.

Solution #2: The problem is almost always that the keys are a mixture of numeric values and text values within the cells and one of the key columns is formatted as GENERAL and the other is formatted as TEXT. Here the left table’s key is formatted as General and the right’s key is formatted as Text.

The first solution that will normally occur to us is “I’ll just format the General column as Text” (or vice-versa).  So we highlight one of the columns and hit Ctrl+F1 (or Home | Format | Format Cells (2007, 2010) or Format | Cells… (2003 & below)) and change the format and …. What!? It doesn’t fix the problem. Changing a cell’s format doesn’t “take” until you edit the cell.  If we have more than just a few rows, we are not going to want to plink back and forth hitting F2 then ENTER a couple hundred times. We have two other options. One is to use Excel’s built-in error correct if it has flagged this for us.  In the screenshot below it has done so.  We can then highlight all of the cells in that column and pick “Convert to Number” from the error correction popup menu.

If we do not have the error correction option or if we simply prefer this method, we can use the Text to Columns tool instead. To use that we highlight the column whose format we wish to change and then from the menu (using Excel 2007 here) select Data | Text to Columns.

We can just leave the DELIMITED option in place and click Next > and then make sure that whatever delimiter we have checked does not actually occur in your column. Normally just sticking with TAB will work fine since it very rarely occurs in a cell.  Then we click Next > again and on Step 3 we pick the format we need General or Text and click Finish.

Providing we correctly remembered what format we needed, we should be rewarded with the VLOOKUP() function working properly.

Alternate Solution – if we’re feeling adventurous, we can do the type conversion in-formula by coercing the data types. If the cells that go into the first VLookup argument are formatted as TEXT and the keys that are in the range that are in the second argument are formatted as GENERAL then something like:

·        =VLOOKUP(IF(ISERR(Key2+0),Key2,Key2+0),Sheet1!Data,2,FALSE) 

will coerce the first argument to GENERAL format whenever the data is numeric in nature.

If it’s the data that’s in the key field of the 2nd argument’s range that is TEXT format and our first argument’s cell is GENERAL format then concatenating an empty string:

·         =VLOOKUP(Key2&””,Sheet1!Data,2,FALSE)

will coerce the numbers in the first argument to all text. [Note: in these examples the second argument is a named range.]

Next most likely solution – If the problem is not a mixture of text and numbers, then the next most common cause is extra spaces on one of the columns. One way we can quickly check this is by finding a cell that has returned #N/A when it shouldn’t and clicking on the key cell and hitting F2 and inspecting the cell’s contents in the formula bar. If there are no leading or trailing spaces there, look at the corresponding key cell in the lookup range the same way. If we confirm that the problem is indeed being caused by leading or trailing spaces and if our data values have no spaces in the middle of them (our data reads “ExcelIsReallyFun     ” or “     ExcelIsReallyRun”; and not “  Excel Is Really Fun”) then the simplest solution would be to just select the cells with too many spaces and hit Ctrl+H (Replace) and enter a space for the Find What argument and nothing at all for the Replace With argument.

If there are spaces within the cells’ values, one solution is to use the TRIM() function. If the extra spaces are in the first argument’s cell, the solution is simple; we just insert TRIM in that first argument:

·     =VLOOKUP(TRIM(KEY),Sheet1!Data, 2, FALSE)

If the extra spaces are in the lookup range then we can insert a helper column and use TRIM() in the helper column to eliminate leading and trailing spaces. We can then leave the helper column and change our VLOOKUP() to match on the helper column, or we can do a Copy and Paste Special… | Values over the top on the helper and then overwrite or delete the original column with the trimmed values. (The easiest way to access Paste Special regardless of version is to right-click once you have copied the cells.)

If our data is always the same length after leading/trailing spaces are removed then we could also use the Fixed Width option on the Text to Columns tool described above and split off any leading and trailing spaces and choose SKIP for the columns of extra spaces.

Problem #3: A member of a leading online Excel forum once quipped that he hated the fact that VLOOKUP() is “Zoolander challenged”. In the movie Zoolander, Ben Stiller plays male model Derek Zoolander, who is not an “ambiturner”. Derek can only turn right on the catwalk; he can’t turn left. Likewise VLOOKUP() can only fetch back data from columns located to the right of the key, it cannot return from columns to the left. Let’s say we need to return the type of flower given the one-letter key from the table below (we’ll assume the table is located in A1:B6).

Solution #3: The way to return data from the left is to understand two additional functions, INDEX() and MATCH().  The MATCH() function also looks something up for us. But instead of returning what’s in the cell, it tells us the position in the list where it matches. The formula =MATCH(“L”,B1:B6,0) will return 4.

The INDEX function returns the value of the contents of a range based on position. So the formula =INDEX(A1:A6, 4) will return “Lilies”.

So if we set that MATCH() inside the INDEX()

·        =INDEX(A1:A6,MATCH(“L”,B1:B6,0))

We now have a formula that can look left and return “Lilies” given the input “L”.

Unfortunately, MATCH() suffers the same inability to convert between numbers and text as VLOOKUP(), so if we have a mix of General and Text, we need to use the same Text to Columns or in-formula coercions as described in Solution #2.

Bonus – Once we become adept at using the team of INDEX() and MATCH() together, we can also employ them in an alternate solution to our composite key problem.  Instead of inserting a helper column, we can have Excel do the concatenation inside the formula by entering the formula as an array formula.

We type the formula in without the braces – the “{“ and “}” – but instead of confirming the formula with a simple ENTER keystroke, we confirm it with CTRL+SHIFT+ENTER. To gain a better understanding of how the array formula actually works we can use the Evaluate Formula tool on the Formula tab in Excel 2007 & 2010, or from the Tools, Formula Auditing, Evaluate Formula… path in Excel 2003. I leave it to the reader to do this exploration.

In summary we now have solutions to three of our most frequent challenges in using the VLOOKUP() function:

• We can now look up based on more than one column.
• We can troubleshoot cells where we’re getting an #N/A though the key is valid.
• We are now “ambiturners” – we can fetch data from columns to the left of our key column.