XL98: Returning the First or Last Match in an Array

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q191112

For a Microsoft Excel 97 and later version of this article, see 214069 (http://support.microsoft.com/kb/214069/EN-US/ ) .

SUMMARY

In Microsoft Excel 98 Macintosh Edition 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.

Back to the top

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:

Back to the top

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.

Back to the top

REFERENCES

For more information about the LOOKUP Function, click the Office Assistant, type "lookup," click Search, and then click to view "LOOKUP."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

179216 (http://support.microsoft.com/kb/179216/EN-US/ ) OFF98: How to Use the Microsoft Office Installer Program

Back to the top

APPLIES TO

Microsoft Excel 98 for Macintosh

Back to the top

kbhowto KB191112

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top