{"id":7747,"date":"2017-06-04T03:48:04","date_gmt":"2017-06-04T03:48:04","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=7747"},"modified":"2017-06-04T03:48:04","modified_gmt":"2017-06-04T03:48:04","slug":"apply-vlookup-duplicate-values","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/apply-vlookup-duplicate-values\/","title":{"rendered":"How to apply Vlookup on duplicate values"},"content":{"rendered":"

Ever came across a situation where you need to apply Vlookup on duplicate lookup values?? Like the one below\u2026<\/p>\n

 <\/p>\n

\"1\"<\/p>\n

 <\/p>\n

Note:- Download the Exercise workbook from here<\/a>.<\/p>\n

Here we have course ID repeating in \u201cLookup Table\u201d. If we simply apply Vlookup in result table, where course ID \u201cA1005\u201d is appearing multiple times, it will always come up with the first instance i.e.\u00a0 participant \u201cAakash\u201d. 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.<\/p>\n

As you may have noticed, I intentionally kept column \u201cA\u201d blank. Here we will create unique values from column \u201cB\u201d, so that it would be easy to apply Vlookup.<\/p>\n

Formula to create unique values:-<\/em><\/strong><\/p>\n

=COUNTIF($B$3:B3,B3)&”-“&B3<\/p>\n

Just type in the above formula in cell \u201cA3\u201d and copy and paste it till cell \u201cA9\u201d. 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.<\/p>\n

 <\/p>\n

\"2\"<\/p>\n

 <\/p>\n

As you may notice, in cell \u201cA7\u201d the course ID \u201cA1005\u201d occurred second time since the beginning of the data, so it is taking this as \u201c2-A1005\u201d and in cell \u201cA9\u201d as \u201c3-A1005\u201d accordingly.<\/p>\n

Now in cell \u201cH3\u201d of \u201cResult Table\u201d we can enter the following vlookup formula with a slight change in lookup value. The reason we are not taking lookup value as cell \u201cG3\u201d simply because it has duplicate values, now as per our new field i.e. \u201cUnique Course ID\u201d in \u201cLookup table\u201d it could be \u201c1-A1005\u201d or \u201c2-A1005\u201d or may be \u201c3-A1005\u201d etc. but not just as \u201cA-1005\u201d.<\/p>\n

The formula will be:-<\/p>\n

=VLOOKUP(COUNTIF($G$3:G3,G3)&”-“&G3,$A$3:$D$9,4,FALSE)<\/p>\n

After putting the above formula in cell \u201cH3\u201d, just copy and paste it down.<\/p>\n

 <\/p>\n

\"3\"<\/p>\n

 <\/p>\n

Note:- Download the Exercise workbook from here<\/a>.<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

Ever came across a situation where you need to apply Vlookup on duplicate lookup values?? Like the one below\u2026
\n <\/p>\n

 
\nNote:- Download the Exercise workbook from here.
\nHere we have course ID repeating in \u201cLookup Table\u201d. If we <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":7751,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[57,232],"class_list":["post-7747","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-excel","tag-vlookup"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7747","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=7747"}],"version-history":[{"count":1,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7747\/revisions"}],"predecessor-version":[{"id":7752,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7747\/revisions\/7752"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/7751"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=7747"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=7747"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=7747"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}