XL: Unexpected Results with Mixed Text and Numbers in Lookup Table (213476)

SYMPTOMS

In Microsoft Excel 97 and later, when you create a lookup table, the lookup functions may return the #N/A error value, or, if you are using the functions in a Microsoft Visual Basic for Applications procedure or macro, you may receive a "type mismatch" error, even though the values in the lookup table are sorted in ascending order and the value being looked up appears to fall within the range of the lookup table values.

This behavior occurs with VLOOKUP(), HLOOKUP(), LOOKUP(), and MATCH() with Match Type arguments of 1 or negative one -1.

CAUSE

This behavior can occur when text values and numeric values are mixed in a lookup table. When you look up a text value, Microsoft Excel looks only at the text values in your table and any numeric values will be overlooked. Similarly, when you look up a numeric value, Microsoft looks only at the numeric values, as in the following example:

Start Excel, and then create the following spreadsheet:

   A1: 3
   A2: 1
   A3: c
   A4: 2
   A5: d
   A6: b

Select cells A1:A6. On the Data menu, click Sort.
In the Sort dialog box, click Ascending under Sort by, and then click OK.

The spreadsheet now appears as:

   A1: 1
   A2: 2
   A3: 3
   A4: b
   A5: c
   A6: d

Type the following formula in any empty cell:
=VLOOKUP("a",A1:A6,1)

The formula returns #N/A instead of the expected 3 (the largest value before the lookup value of "a").

RESOLUTION

To resolve this behavior, change all of the characters to text, use a cell reference, and enter the formula as an array, as in the following steps:

NOTE: The steps in this example are a continuation of the example in the "Cause" section of this article.

Type the following in cell B1:
a
Type the following formula in cell B2 (or any empty cell)
=VLOOKUP(TEXT(B1,"@"),TEXT(A1:A6,"@"),1)
Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
The formula returns "3", the largest value before the lookup value of "a" referenced by cell B1.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

A Microsoft Visual Basic macro that uses a lookup function returns the following run-time error message

Type mismatch

when you search a range that includes both text and numeric values. To illustrate this behavior, use the sample Visual Basic macro in the following example:

Repeat steps 1 through 5 of the example in the "Cause" section.
Select cells A1:A6.
On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, type hi and then click OK.
Press ALT+F11 to start the Visual Basic editor.
On the Insert menu, click Module.

In the module sheet, type the following code:

Sub Test()
   MsgBox Application. _
   VLookup("b1", Workbooks("book1.xls"). _
   Worksheets("sheet1").Range("hi"), 1, False)
End Sub

Press ALT+F11 to return to Excel.
On the Tools menu, point to Macro, and then click Macros.
In the Macro name box, click Test, and then click Run.
The macro returns the following run time error message:
Type mismatch

REFERENCES

For more information about the Vlookup() worksheet function, click Microsoft Excel Help on the Help menu, type vlookup in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Hlookup() worksheet function, click Microsoft Excel Help on the Help menu, type hlookup in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Lookup() worksheet function, click Microsoft Excel Help on the Help menu, type lookup in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Match() worksheet function, click Microsoft Excel Help on the Help menu, type match in the Office Assistant or the Answer Wizard, and then click Search to view the topic.