Microsoft KB Archive/153591

{|
 * width="100%"|

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

 * }

-

The information in this article applies to:


 * 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
 Type the following information into a spreadsheet:  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
 Type the following information into a spreadsheet:  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 Varied 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

Technology :