Microsoft KB Archive/51028

{| = Excel 1.5-2.2: VLOOKUP, HLOOKUP, LOOKUP Return #REF! Error =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q51028

SUMMARY
When using a formula in Microsoft Excel versions 1.50 and 2.20 to calculate the index argument in a VLOOKUP formula [or HLOOKUP or LOOKUP], where the index formula is located in another cell, a #REF! error may appear upon recalculation. For example:

A              |  B  |  C  |  D  |  E   - 1| =VLOOKUP(A3,B1:D5,A2)     |  a  |  1  |  5  |  1 2| =IF(A4<10,1,IF(A4<20,2,3)) | b  |  2  | 10  |  3 3| c                         |  c  |  3  | 15  |  5 4| 9                         |  d  |  4  | 20  |  7 5|                           |  e  |  5  | 25  |  9 Cell A1 contains the VLOOKUP formula and the index argument is referenced to cell A2. Cell A2 contains a formula to calculate the index argument. If cell A4 is changed (which changes the index argument), a #REF! error appears in cell A1 and does not disappear until the worksheet is saved, closed, and reopened.

If you embed the index formula within the VLOOKUP formula, the problem does not occur. For example, cell A1 should read as follows:

=VLOOKUP(A3,B1:D5,IF(A4<10,1,IF(A4<20,2,3))) This problem was corrected in Microsoft Excel version 3.0.
 * }