REVERSE LOOKUP WITH DUPLICATES IN EXCEL

It is good to be able to do a Reverse Lookup but it is Awesome if you know how to return Multiple Items.

Before you continue with the current article, Please revisit the Previous Article on reverse lookup as this is a continuation.

Suppose you are managing a project and you have below scheduled dates, times & jobs. Can you find the days, Times and Jobs assigned to staff Joy Bell?

The trick in returning multiple items is to use the SMALL/LARGE function to manage the multiple row/column numbers.

Multiple DateTime Values

{=IFERROR(
     SMALL(
          IF(($D$5:$G$15=$I$4),($D$4:$G$4+$B$5:$B$15))
     ,ROW(A1))
,"")}

►As shown in the previous article, IF(($D$5:$G$15=$I$4),($D$4:$G$4+$B$5:$B$15) returns an array of the DateTime values where the criteria are met.

{FALSE,FALSE,FALSE,42504.3333333333;FALSE,FALSE,FALSE,FALSE;
42501.4166666667,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,42503.625,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}

►This array is fed into SMALL function and  ROW(A1)=1 provides SMALL with k as you scroll down.

=SMALL({42504.3333333333,42501.4166666667,42503.625},1)

►IFERROR checks for error and returns blank when the SMALL function has returned ALL DateTime Values.

Multiple Corresponding Jobs

Retuning the multiple jobs is tricky as the job assigned depends on the Date.

From the above example, Joy Bell starts with Job 3, Job 8 then Job 1. These are in different rows thus the simple INDEX & MATCH cannot apply here.

{=IFERROR(
  INDEX($C$5:$C$15,
   MATCH(TRUE,
    INDEX($D$5:$G$15,,
      SMALL(
        IF($D$5:$G$15=$I$4,COLUMN($D$4:$G$4)-COLUMN($D$4)+1)
      ,ROW(A1)
     ))=$I$4,0)),
  "")}

►The trick is to find the column with the earliest date first before selecting the job. Below INDEX & SMALL functions return Columns from the earliest date

INDEX($D$5:$G$15,,
  SMALL(
     IF($D$5:$G$15=$I$4,COLUMN($D$4:$G$4)-COLUMN($D$4)+1),ROW(A1)))

►After the column with the earliest date is selected, the MATCH function checks the row that meets the criteria.

►Then INDEX  function returns the Job that is in the row number as returned by MATCH

Multiple DateTime & Job Values

If you would like to return the DateTime & Job assigned as a single value, then you write a formula that Concatenates both values.

{=IFERROR(
  TEXT(
   SMALL(
    IF($D$5:$G$15=$I$4,($D$4:$G$4+$B$5:$B$15))
    ,ROW(A1)),"dd-mm-yyyy hh:mm AM/PM")
&" "&
  INDEX($C$5:$C$15,
   MATCH(TRUE,
     INDEX($D$5:$G$15,,
      SMALL(
        IF($D$5:$G$15=$I$4,COLUMN($D$4:$G$4)-COLUMN($D$4)+1)
     ,ROW(A1)))=$I$4,0)),"")}

►The trick here is formatting the DateTime Value first before concatenating it with the Job. You can use TEXT function for this formatting

TEXT(
  SMALL(
    IF($D$5:$G$15=$I$4,($D$4:$G$4+$B$5:$B$15))
   ,ROW(A1))
,"dd-mm-yyyy hh:mm AM/PM")

NB: You can replace SMALL Function with AGGREGATE to create a non-array function

=IFERROR(
  AGGREGATE(15,6,(($D$4:$G$4+$B$5:$B$15)/($D$5:$G$15=$I$4)),ROW(A1)),"")

How it works;

The trick in using the AGGREGATE function is to define the array for SMALL function

In our example, the array is all the Date + Time Value that meets our criteria

($D$4:$G$4+$B$5:$B$15) Returns All Date + Time Value
s
{42501.3333333333,42502.3333333333,42503.3333333333,
42504.3333333333;42501.375,42502.375,42503.375,
42504.375;42501.4166666667,42502.4166666667,
42503.4166666667,42504.4166666667;42501.4583333333,
42502.4583333333,42503.4583333333,42504.4583333333;
42501.5,42502.5,42503.5,42504.5;42501.5416666667,
42502.5416666667,42503.5416666667,42504.5416666667;
42501.5833333333,42502.5833333333,42503.5833333333,
42504.5833333333;42501.625,42502.625,42503.625,
42504.625;42501.6666666667,42502.6666666667,
42503.6666666667,42504.6666666667;42501.7083333333,
42502.7083333333,42503.7083333333,42504.7083333333;
42501.75,42502.75,42503.75,42504.75}

($D$5:$G$15=$I$4) Returns a Boolean array, TRUE where criteria is met otherwise FALSE

{FALSE,FALSE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE;
TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE;
FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE}

NB: The boolean array is converted to its numeric equivalent where TRUE = 1 & FALSE =0 during division

So, when you divide ALL date + time Values with this array of 1 & o, it will return the date + time value where criteria are TRUE (1) and Error where criteria are FALSE (0)

{#DIV/0!,#DIV/0!,#DIV/0!,42504.3333333333;#DIV/0!,#DIV/0!
,#DIV/0!,#DIV/0!;42501.4166666667,#DIV/0!,#DIV/0!,#DIV/0!;
#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,#DIV/0!,
#DIV/0!;#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,
#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,42503.625,#DIV/0!;#DIV/0!,
#DIV/0!,#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!;
#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}

And since we had selected Ignore Error values in our options

Then the Final array that SMALL function loops through is like one below

{42504.3333333333,42501.4166666667,42503.625}

Watch these Videos on the AGGREGATE Function.

DOWNLOAD WORKSHEET

RELATED ARTICLES

REVERSE LOOKUP IN EXCEL

RECOMMENDED READING

Mr Excel on Reverse lookup with Duplicates

https://www.exceluser.com/excel_help/functions/function-aggregate.htm