XL: VLOOKUP()/HLOOKUP() Return Incorrect Value with TFE or AEE

ID: Q87442

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, when you use the VLOOKUP and HLOOKUP functions, the incorrect value or #VALUE error message is returned if Transition Formula Evaluation (TFE) (Excel version 5.0 or later) or Alternate Expression Evaluation (AEE) is selected (Excel version 4.0).

CAUSE

VLOOKUP and HLOOKUP return the index in the lookup array instead of the value at that index if TFE or AEE 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 or AEE by using one of the following methods.

Microsoft Excel Versions 5.0 and Later

To disable Transition Formula Evaluation, follow these steps:

On the Tools menu, click Options (or Preferences). Click the Transition tab.

Under Sheet Options, clear the Transition Formula Evaluation check box, and click OK.

Microsoft Excel Version 4.0

To disable Alternate Expression Evaluation, follow these steps:

On the Options menu, click Calculation.

Under Sheet Options, clear the Alternate Expression Evaluation check box and click OK.

MORE INFORMATION

Transition Formula Evaluation and Alternate Expression Evaluation are options 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 or AEE 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 or AEE is enabled and the value "Blue" if TFE or AEE is not enabled.

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

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, page 57

"Switching to Microsoft Excel from Lotus 1-2-3," version 4.0, pages 10-11

"Microsoft Excel Function Reference," version 4.0, pages 221-222, 450- 451

Additional query words: 5.00a 5.00c 7.00a 97 98 XL98 XL97 XL7 XL5 XL4

Keywords : kb3rdparty
Version : WINDOWS:4.0,4.0a,5.0,5.0c,7.0,7.0a,97; MACINTOSH:4.0,5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbprb