Reverse lookup with duplicates

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 revist 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?

Reverse lookup with duplicates

The trick in returning multiple items is to use 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 articleIF(($D$5:$G$15=$I$4),($D$4:$G$4+$B$5:$B$15) returns an array of the DateTime values where the criteria is 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 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 returns 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, 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 AGGREGATE function is to define the array for SMALL fucntion

capture

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 Values
{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 equiavlent 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 is TRUE (1) and Error where criteria is 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

AGGREGATE FUNCTION

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

{42504.3333333333,42501.4166666667,42503.625}

Watch these Videos on AGGREGATE Function.

DOWNLOAD WORKSHEET

RELATED ARTICLES

REVERSE LOOKUP IN EXCEL

RECOMMENDED READING

Mr Excel on Reverse lookup with Duplicates

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

Leave a Comment:

Your email address will not be published. Required fields are marked *