VLOOKUP Multiple Occurrence

Objective

Many a times, while searching a list, the searched text/value may appear more than once. Standard VLOOKUP will return first occurrence from top. The question is how to find second, third or fourth occurrence?

 
Prerequisites

The reader/user should have complete understanding of VLOOKUP, MATCH, COUNTIF & INDIRECT Functions and ”&” operator.

Solution Concept

MATCH will always search Top to Bottom. If we start search for second occurrence from the row next to first occurrence it will get us the result. For example if first occurrence is appearing in 9^th row then for second occurrence our search should start from 10th row.

Once we know in which rows out result is located, we can easily pick values with help of INDIRECT.

Let’s do it

1.1.   Use COUNTIF to find number of occurrence (Checking only, not necessary)

1.2.      Use MATCH to find first Occurrence

1.3.     Find Excel row of occurrence, Match will give us Row number of Range, whereas we need Row number of Excel

1.4.      Add Column Alphabet with Excel Row to create valid Cell reference

1.5.      With INDIRECT pick result

Sample Workbook          

Prerequisites/See also (Links not activated yet)