This is a quick and dirty formula trick to search for an item in a list and get the 2nd, 3rd, 4th . . .  occurrence of that item found.

This is a quick and dirty formula trick to search for an item in a list and get the 2nd, 3rd, 4th . . .  occurrence of that item found.

As you probably know, VLOOKUP (as well as MATCH and HLOOKUP) only gives you the first occurrence of that item in a list.

This formula uses the trick of searching for an item and returning the row number of the of 2nd, 3rd, 4th . . . occurrence of that item.

It your list of items are in Column “A”, the item that you want to search for is in cell “D3” and the listing of occurrences are in Column “D”, then you can easily set of your spreadsheet in the following manner:

 

Return the xth occurrence of an item in an Excel list.