Forum Discussion

mhapij's avatar
mhapij
Copper Contributor
Mar 12, 2018

VLookup or IF Then or Match/Index? Not sure getting dup results using Vlookup

I am trying to search drop down column A in the Master sheet, match data and then pull that data's row into the corresponding sheet (car, sedan, suv).

I get the data to pull in using a Vlookup but I'm pulling in duplicates as well.  How can I fix the formula to re-read the array without pulling in dups?  Or this there a better way to search and paste this data from master sheet to corresponding sheets.

Thanks

Why are duplicates in row 5,6,7,8 being populated?

 A3 =IFERROR(VLOOKUP($A$1,Master!$A$2:$D$15,1,FALSE),"")

B3 =IFERROR(VLOOKUP($A$1,Master!$A2:$D$11,2,FALSE),"")

C4 =IFERROR(VLOOKUP($A$1,Master!$A2:$D$11,3,FALSE),"")

D5=IFERROR(VLOOKUP($A$1,Master!$A2:$D$11,4,FALSE),"")

.

.

.

.

.

A12 =IFERROR(VLOOKUP($A$1,Master!$A11:$D$15,4,FALSE),"")

Master sheetCar

 

  • Hi,

     

    If that's only to copy filtered table to another sheet better to use Power Query (Get&Transform in 2016). 

    • mhapij's avatar
      mhapij
      Copper Contributor

      Thank you! I have never heard of this command so I will check it out.  Much appreciated.

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi,

     

    VLOOKUP won't work in this case!

    The lookup_value argument requires a unique identifier!

    Otherwise, VLOOKUP will return the corresponding values of the first match cell and ignore the other dups.

    So you will always get the same corresponding values.

     

    The solution in your case is to use the Get & Transform Data (Aka Power Query) as Sergei mentioned before.

    Please find the attached file to see this solution.

     

    Also, one of the benefits of using Power Query is when adding new records in the master sheet, you can refresh the tables in the sub-sheets to update them with these new records.

  • In addition, if work with formulas that could be

     

    =IFERROR(INDEX(Table1[#Data],AGGREGATE(15,6,ROW(Table1[#Data])/(Table1[Category]=$F$1),ROWS(Master!$A$2:Master!$A2))-ROW(Table1[[#Headers],[Category]]),1),"")

    If source master is not structured as Table that could be a range (Master!$B$2:$D$11 instead of Table1[#Data]).

Resources