How to use the LOOKUP function with unsorted data in Excel

Article translations Article translations
Article ID: 181212 - View products that this article applies to.
This article was previously published under Q181212
Expand all | Collapse all

On This Page

SUMMARY

In Microsoft Excel, the LOOKUP worksheet function has a vector form and an array form. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The other form of LOOKUP (array form) automatically looks in the first column or row. 

 LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order. The following information describes different formulas that you can use to return the same information returned by LOOKUP without requiring that the first column of the table be sorted. 

MORE INFORMATION

You can use a combination of the INDEX and MATCH functions, a combination of the OFFSET and MATCH functions, HLOOKUP, or VLOOKUP to provide the same functionality as LOOKUP. None of these choices require that the lookup table be sorted, unlike the LOOKUP function. 

Using INDEX and MATCH

The following formula returns the same information that a LOOKUP 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
                    "table_array".

   Lookup_Array   = The range of cells containing possible
                    lookup values.

   Col_Index_Num  = The column number in "table_array" 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

You can use the OFFSET function if you are unsure of the entire address of "table_array" because it uses the MATCH function to locate the position of "lookup_value" within "lookup_array." In the following function, the "offset_col" argument uses the upper-left cell of the table as "top_cell" and returns the value you want as determined by the MATCH (offset row) and offset column: 
   =OFFSET("top_cell",MATCH("lookup_value","lookup_array",0),"offset_col")
				

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 "Name" range is not sorted, using LOOKUP to find the age of Mary returns 28, the age of Henry. To find the age of Mary, use the following: 
   =OFFSET(A1,MATCH("Mary",A2:A5,0),2)
				
or
   =LOOKUP("Mary", A2:A5, C2:C5)
				
Both the functions return 22.

HLOOKUP and VLOOKUP

If you are looking for an exact match in Microsoft Excel, the first column of a lookup table does not have to be sorted to use the VLOOKUP and HLOOKUP functions. 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 earlier also works in Microsoft Excel, but is not necessary.  

Note Use the VLOOKUP and HLOOKUP functions instead of the array form of LOOKUP because VLOOKUP and HLOOKUP do not require the lookup table to be sorted if the range_lookup argument is FALSE.

For more information about how to use the VLOOKUP function to find an exact match in Microsoft Excel, click the following article number to view the article in the Microsoft Knowledge Base:
181213 How to use VLOOKUP or HLOOKUP to find an exact match

Properties

Article ID: 181212 - Last Review: September 18, 2011 - Revision: 9.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
Keywords: 
kbformula kbfunctions kbprogramming kbhowto KB181212

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com