To find the first value instead of the last value in an array, use the INDEX() and MATCH() functions.
In a new worksheet, type the following data:
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.
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
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.
Article ID: 214069 - Last Review: Apr 29, 2008 - Revision: 1