XL: VLOOKUP & HLOOKUP May Return #N/A Error

• Microsoft Excel for Windows, versions 4.x, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 4.x, 5.0, 5.0a

SYMPTOMS

If you use a text string as the lookup value in a VLOOKUP worksheet function, you may receive the #N/A error value. This error will also occur with the HLOOKUP function.

CAUSE

The versions of Microsoft Excel mentioned above, do not automatically detect headers from a lookup table and strip them out. Therefore, when you specify the lookup range argument, do not include column headings in the selection. Or, or use the INDEX and MATCH function, instead.

WORKAROUND

To work around this problem, do not include the headers in the lookup range argument as demonstrated below.

Example Using VLOOKUP

1. Type the following information into a spreadsheet:
   
   

   
         A1:  Code     B1: Amount     C1:
         A2:  a        B2: 100        C2:
         A3:  b        B3: 200        C3: 




2. Type the following formulas in cells C2 and C3:
   
   

   
         C2: =VLOOKUP("a",A2:B3,2)
         C3: =INDEX(A1:B3,MATCH("a",A1:A3,0),2) 

   
   
   Both of these formulas return the correct answer of 100.

Example Using HLOOKUP

1. Type the following information into a spreadsheet:
   
   

   
         A1: Code       B1: a       C1: b       D1:
         A2: Amount     B2: 100     C2: 200     D2:
         A3:            B3:         C3:         D3: 




2. Type the following formulas in cells D2 and D3:
   
   

   
         D2: =HLOOKUP("a",B1:C2,2)
         D3: =INDEX(A1:C2,2,MATCH("a",A1:C1,0),2) 

   
   Both of these formulas return the correct answer of 100.

STATUS

Microsoft has confirmed this to be a problem in the versions Microsoft Excel mentioned above. This problem has been corrected in Microsoft Excel for Windows 95, version 7.0.

MORE INFORMATION

For additional information, please see the following article in the Microsoft Knowledge Base:

Q113261 : Unexpected Results with Mixed Text and Numbers in Lookup Table

Q77114 :Performing a Lookup with Unsorted Data in Excel

Additional query words: 4.00 4.00a 5.00a 5.00c

Keywords          : 
Version           : WINDOWS:4.x,5.0,5.0c;MACINTOSH:4.x,5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        : kbbug 

Last Reviewed: April 22, 1999