Forum Discussion
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).
Thank you! I have never heard of this command so I will check it out. Much appreciated.
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.
Awesome -- Thank you!!!
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]).
