Excel Magic Trick 822: 4-Way Lookup When There ARE Duplicates (Return Multiple Items)


About this tutorial:

Video duration: 13:37
Download file:
See how to do a four way lookup when there ARE duplicates and you need to return multiple items from one lookup value
1. See how to do a Four way lookup when there are duplicate values to return from 1 lookup value
2. See a Helper Column Method
3. See an array formula method using AGGREGATE 2010 Excel function
4. See an array formula method using SMALL 2007 or 2003 Excel function

See related Video:
Excel 2010 Magic Trick 821: 4-Way Lookup When There Are No Duplicates 2007 and 2003 Methods


  1. the same issue of duplicate entries I am having but one step further I am looking forward to select particular size of ship container horizontally and even from that select the cheapest provider. Can you please provide example of five way lookup or six way lookup for duplicate entries.

  2. I usually work with very large sets of data like hundred thousand to millions of rows with duplicate entries. Is there an excel function to efficiently remove both duplicated data? Would welcome any assistance. Thanks!

  3. @BPOWFM , most of the new functions are statistics, Unless you do statistics, there are not of much use. Here is a play list with about 40 videos with many of the new functions:

    youtube [dot] com/user/ExcelIsFun#grid/user/A1F22CE787E693BF

  4. Aggregate function it is an improvement. as always an excellent video. The designers of Microsoft Excel owe u big time as through your videos u have advertise the advantages of using excel formulas.


Please enter your comment!
Please enter your name here