Search code examples
excelexcel-formulaexcel-2007

Returning multiple unique values with a vlookup or index match formula

Column A Column B Column C Date Deal Number Item Number 1/28/2018 201809665 1011943002 2/4/2018 201809665 1011943002 2/18/2018 201809665 1011943002 11/5/2017 201745256 1018100005 11/12/2017 201745256 1018100005 11/19/2017 201745256 1018100005 11/26/2017 201745256 1018100005 1/28/2018 201809288 1018100005 2/4/2018 201809288 1018100005 2/11/2018 201809288 1018100005 2/18/2018 201809288 1018100005

I have a table that looks like the one above, the data is unique based off of the week, deal number, and item number data. I want to figure out how to retrieve all of the date values (from column A) that correspond to a specific item number.

In this example, if I selected Item Number: 1011943002 it would return: 1/28/2018 2/4/2018 2/18/2018

Solution

  • In E2 enter:

    =IFERROR(INDEX($A$2:$A$12, AGGREGATE(15, 6, ROW($1:$999)/($C$2:$C$12=1011943002), ROW(1:1))),"")

    and copy downwards:

    enter image description here

    This assumes that for each item number, the list of dates is unique.