XL: How to Return the First or Last Match in an Array

Article translations Article translations
Article ID: 214069 - View products that this article applies to.
This article was previously published under Q214069
For a Microsoft Excel 98 version of this article, see 191112.
Expand all | Collapse all

SUMMARY

You can use the LOOKUP() function to search for a value within an array of sorted data and return the corresponding value contained in that position within another array. If the lookup value is repeated within the array, it returns the last match encountered. This behavior is true for the VLOOKUP(), HLOOKUP(), and LOOKUP() functions.

To find the first value instead of the last value in an array, use the INDEX() and MATCH() functions.

MORE INFORMATION

The following example contrasts the results that you receive when you use the LOOKUP() function with the results that you receive when you use the INDEX() and MATCH() functions.

In a new worksheet, type the following data:
   A1: 1   B1: Red    C1: =LOOKUP(1,A1:A4,B1:B4)
   A2: 1   B2: Blue   C2: =INDEX(A1:B4,MATCH(1,A1:A4,0),2)
   A3: 2   B3: Orange
   A4: 3   B4: Yellow
				
In this example, cell C1 returns the last match of the value 1, resulting in the text string "Blue" being returned to the cell. However, cell C2 returns the first match of the value 1, resulting in the text string "Red" being returned to the cell.

NOTE: When you use the INDEX() and MATCH() functions, the lookup array does not need to be sorted in ascending order; however you must specify a match_type argument of 0 (zero) to return the correct value.

Properties

Article ID: 214069 - Last Review: January 24, 2007 - Revision: 3.2
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowto KB214069

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