• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlook/Index up not working for selecting all duplicates

S

shankar_iyer83

Member
Hi,

I have two excel files. and when I use Vlookup to copy values from one file (sample original file) to another (similar file) , its not working for duplicates. After checking, I see there are lots of duplicates and its picking first value. How do I put it to get all values ? I tried to use index/match but could not figure out

I am attaching sample files with some duplicates. Please note that there are lots of duplicates and I want all of them
 

Attachments

  • Sample original file.xlsx
    8.8 KB · Views: 14
  • similar file.xlsx
    11.9 KB · Views: 11
@Rahul,
Thanks, The issue here I cant modify two files (add rows/delete rows), so if I do not use column E and simply use formule,will it work? Secondly, I need to refer to other file if I reference to that , is it fine?
 
shankar_iyer8, Rahul's formula is fine; he just brought both lists onto the same sheet for easier demonstration and development of the formula. The following formula is largely the same (it follows the idea in the first link provided by Narayan, now it refers to different workbooks and sheets). In cell H5 of your similar file.xlsx ARRAY-ENTER the following while your Sample original file.xlsx is open:
Code: 
=INDEX('[Sample original file.xlsx]Sheet1'!$G:$G,SMALL(IF($G4='[Sample original file.xlsx]Sheet1'!$F$5:$F$16,ROW('[Sample original file.xlsx]Sheet1'!$F$5:$F$16)),COUNTIF($G$4:G4,G4)))
then copy down.

Array-Entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.
 
You must log in or register to reply here.
Back
Top