Excel: Returning the First or Last Match in an Array

 Last reviewed: February 2, 1998
Article ID: Q104224

The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel 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 will return 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, you can use the INDEX() and MATCH() functions.

MORE INFORMATION

The example below contrasts the results you get when you use the LOOKUP() function with the results you get when you use the INDEX() and MATCH() functions:

Example

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 worksheet, cell C1 will return the last match of the value 1, resulting in the text string "Blue" being returned to the cell. However, cell C2 will return the first match of the value 1, resulting in the text string "Red" being returned to the cell.

Note that 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 in order to return the correct value.

REFERENCES

"Function Reference," version 4.0, pages 221, 236, 264, 267, 450 "Function Reference," version 3.0, pages 123, 128, 145, 148, 246

Additional query words: howto
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.