Microsoft KB Archive/214311

= XL2000: VLOOKUP/HLOOKUP Functions Return Incorrect Value with TFE =

Article ID: 214311

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214311





SYMPTOMS
In Microsoft Excel, when you use the VLOOKUP and HLOOKUP functions with Transition Formula Evaluation (TFE) selected, the incorrect value or #VALUE error message is returned.



CAUSE
VLOOKUP and HLOOKUP return the index in the lookup array instead of the value at that index if TFE is selected and the col_index_num (row_index_num for HLOOKUP) argument is set to 1.



WORKAROUND
To return the correct value when you use the VLOOKUP or HLOOKUP function, disable TFE. To disable Transition Formula Evaluation, follow these steps:
 * 1) On the Tools menu, click Options. Click the Transition tab.
 * 2) Under Sheet Options, click to clear the Transition formula evaluation check box and click OK.



MORE INFORMATION
The Transition Formula Evaluation option is designed to allow for differences between the way Microsoft Excel and Lotus 1-2-3 evaluate expressions. This option is automatically enabled when you open a Lotus 1-2-3 worksheet in Microsoft Excel.

The VLOOKUP and HLOOKUP functions search the first column of an array (top row with HLOOKUP) for a particular value and return the value in the cell indicated by the index argument. However, these functions may return the incorrect value if TFE is enabled.

Example
To use this example, enter the following data:   A1: Blue   B1: 10 A2: Green B2: 20 A3: Red   B3: 30 The formula =VLOOKUP("Blue",A1:B3,1) returns the value 0 (array index to cell A1) if TFE is enabled and the value "Blue" if TFE is not enabled.

NOTE: Array indexes start with 0 so the index to A1 is 0, A2 is 1, and so on.

Additional query words: XL2000

Keywords: kbprb kb3rdparty KB214311

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.