Microsoft KB Archive/77114

{|
 * width="100%"|

Performing a Lookup with Unsorted Data in Excel

 * }

Q77114

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.0, 2.01, 2.1, 2.10c, 2.10d, 3.0, 3.0a, 4.0, 4.0a, 4.0c
 * Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0
 * Microsoft Excel for the Macintosh, versions 2.20, 2.2a, 3.0, 3.0a, 4.0

-

SUMMARY
In Microsoft Excel, the VLOOKUP function searches the leftmost column of an array for a particular value and returns the corresponding value from a cell in another column, indicated by the col_index_num.

VLOOKUP requires that the first column of the lookup table is sorted in ascending order. The following information describes different formulas that you can use to return the same information returned by VLOOKUP without requiring that the first column of the table be sorted.

Using INDEX and MATCH
The following formula return the same information that a VLOOKUP returns without requiring the first column of the table to be sorted

  =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)

where:

Table_Array   = The entire lookup table

Lookup_Value  = the value to be found in the first column of                    &quot;table_array&quot;

Lookup_Array  = the range of cells containing possible lookup values

Col_Index_Num = the column number in &quot;table_array&quot; for which the matching value should be returned

If none of the cells in Lookup_Array match Lookup_Value, this formula will return #N/A.

Using OFFSET and MATCH
The OFFSET function can be used if you are unsure of the entire address of &quot;table_array&quot; because it uses the MATCH function to locate the position of &quot;lookup_value&quot; within &quot;lookup_array.&quot; In the following function, the &quot;offset_col&quot; argument uses the upper left most cell of the table as &quot;top_cell&quot; and returns the desired value as determined by the MATCH (offset row) and offset column:

  =OFFSET(&quot;top_cell&quot;,MATCH(&quot;lookup_value&quot;,&quot;lookup_array&quot;,0),&quot;offset_col&quot;)

Example
  A1: Name        B1: Dept        C1: Age A2: Henry      B2: 501         C2: 28 A3: Stan       B3: 201         C3: 19 A4: Mary       B4: 101         C4: 22 A5: Larry      B5: 301         C5: 29

Because the &quot;Name&quot; range is not sorted, using VLOOKUP to find the age of Mary returns 28, the age of Henry. To find the age of Mary, use the following:

  =OFFSET(A1,MATCH(&quot;Mary&quot;,A2:A5,0),2)

The function returns 22.

NOTE: In Microsoft Excel 5.0, the first column of a lookup table does not have to be sorted to use the VLOOKUP and HLOOKUP functions if you are looking for an exact match. To look for an exact match, specify the fourth argument of VLOOKUP or HLOOKUP as FALSE. If you omit the fourth argument, or specify the argument as TRUE, you must sort the first column of the table. The OFFSET function described above also works in Microsoft Excel versions 5.0, but is not necessary.

For additional information about using the VLOOKUP function to find an exact match in Microsoft Excel version 5.0, please see the following article in the Microsoft Knowledge Base:

Q108639 : XL: Range_Lookup Argument in VLOOKUP Finds Exact Match