How to apply Vlookup on duplicate values

Ever came across a situation where you need to apply Vlookup on duplicate lookup values?? Like the one below…

Note:- Download the Exercise workbook from here.

Here we have course ID repeating in “Lookup Table”. If we simply apply Vlookup in result table, where course ID “A1005” is appearing multiple times, it will always come up with the first instance i.e. participant “Aakash”. Now to get rid of this common problem, we have few solutions. In this post I will share one of the easiest ways to do this task.

As you may have noticed, I intentionally kept column “A” blank. Here we will create unique values from column “B”, so that it would be easy to apply Vlookup.

Formula to create unique values:-

=COUNTIF($B$3:B3,B3)&”-“&B3

Just type in the above formula in cell “A3” and copy and paste it till cell “A9”. This formula will count the number of times a course ID coming in the given range as this formula goes down. After that it will join the count with its course ID. This way we will get unique course IDs.

As you may notice, in cell “A7” the course ID “A1005” occurred second time since the beginning of the data, so it is taking this as “2-A1005” and in cell “A9” as “3-A1005” accordingly.

Now in cell “H3” of “Result Table” we can enter the following vlookup formula with a slight change in lookup value. The reason we are not taking lookup value as cell “G3” simply because it has duplicate values, now as per our new field i.e. “Unique Course ID” in “Lookup table” it could be “1-A1005” or “2-A1005” or may be “3-A1005” etc. but not just as “A-1005”.

The formula will be:-

=VLOOKUP(COUNTIF($G$3:G3,G3)&”-“&G3,$A$3:$D$9,4,FALSE)

After putting the above formula in cell “H3”, just copy and paste it down.