Vlookup – Multiple Results with VBA

Associated Files Download Links


Vlookup Text

The standard Vlookup Function can be used to find a value within a table:
251 find nth occurence
And we would use VLOOKUP like so:

to give the value 30.

However, in this list we see that Dog occurs 3 times. The standard VLOOKUP function will only return the value associated with the first item in this list. It won’t return the 125 or 9,250 with the 2nd or 3rd instance of “dog” in this list.

Vlookup Multiple Results

The following function allows us to specify a range, an expression to be searched for, and the instance (result number) and then return the corresponding value:

The main difference between this and the standard VLOOKUP function is that in this case, the range is the only the range of labels – not the entire data range.
The following is a subroutine that calls this function based on the click event from a command button. It looks in the range A1:A8 on Sheet2, for the 3rd instance of the word Dog:

The variable “Answer” stores the result of the function – which is then displayed in a Msgbox on the screen:
252 find nth occurence
>However if the word can’t be found in the list or the frequency does not occur e.g there isn’t a 5th instance of the word “Dog”, then the value of 1,000,000 is returned :-
Answer = Find_nth_Occurrence(Sheets(“Sheet2”).Range(“A1:A8”), “Dog”, 5)
Or
Answer = Find_nth_Occurrence(Sheets(“Sheet2”).Range(“A1:A8”), “Horse”, 2)

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

Learn More!


<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues: