Leading the way in Microsoft Office Development

Looking up a particular occurrence

The VLOOKUP() and HLOOKUP() functions can only be used to find the first occurrence of an element in a "table_array".

You can use an array formula to return a particular occurrence of a value when your table contains duplicate entries.

This is possible using a combination of the INDEX() function and the SMALL() function.

Lets imagine a simple table of data and lets suppose you want to find the Age of particular occurrence of "Russell" in this table.

The SMALL() function can be used to obtain the particular occurrence you are looking for.

The ROW() function can be used to obtain the row numbers for the items in the table.

The following formula will return a particular occurrence of a value in a column that contain duplicates.

"match" is the text string to match in the first (leftmost) column.

"row_item" is the occurrence number to return.

"col_index" is the column number to return in the table.

2nd Occurrence

Lets imagine you want to find the age of the second occurrence of Russell in this table.

The following formula will return the "Age" of the second occurrence of Russell in the table.

ROW(B2:B14) returns the row numbers corresponding to all the items in the table: {2,3,4,5 …}

ROW(B2) returns the row number of the first item in the table: {2}

Subtracting the two gives you an array starting from zero {0,1,2,3, …} for as many rows there are in the table.

The +1 just re-indexes the array to start at 1 instead of zero: {1,2,3,4,…}

ROW(B14) + 1 returns the row number that is directly below the table.

This is to ensure that when the IF function does not find a match the (row) number returned is larger than any row in the table.

In this case the number 15 is returned.

In this example we are looking for all the occurrences of "Russell" so the IF function will return the following array:

{15,15,3,15,15,15,7,15,15,15,11,15,15}

We are looking for the 2nd occurrence so the SMALL function returns the second smallest number which is 7.

This row number is then passed to the INDEX function to return the value in the Age column, column 2.

3rd Occurrence

The following formula will return the "Age" of the third occurrence of Gary in the table.

Last Occurrence

If you want to find the last occurrence of an item in a list then you can replace the "row_item" with the COUNTIF() function.

This function returns the total number of values in a range that match a particular item.