Excel: find value in column, then search for date value in that row, return value to the left

Question

I am looking for a formula (or code) that finds a value in a column, then find the value in that row between dates, when multiple dates return the max, then returning the value to the left. I cannot use a helper column. I tried a couple of nested index/match formula's but without success...

For the below example:

Parameter 1: Find Fork in Column A

Parameter 2: In that row (4), find date between: 1 Jan 18 AND 5 Jan 18

Parameter 3: when multiple dates are found in that date range, return the max

Parameter 4: Return the value from column left of that date

Result: black in Column D.

    A       B       C           D       E           F             G
1   ID      Colour 1Date 1      Colour 2Date 2      Colour 3      Date 3
2   Plate   Green   1-Jan-18    Red     23-Jan-18   L blue        14-Feb-18
3   Bowl    Blue    6-Jan-18    Brown   28-Jan-18   Yellow/Green  19-Feb-18
4   Fork    Yellow  2-Jan-18    Black   4-Jan-18    Turquoise     24-Feb-18
5   Knive   Purple  16-Jan-18   White   7-Feb-18    Maroon        1-Mar-18
6   Spoon   Pink    21-Jan-18   Orange  12-Feb-18   L pink        6-Mar-18

I hope it is possible! Many many thanks in advance.

chris neilsen · Accepted Answer · 2018-06-25 01:45:51Z

1

Try this

where
I10 = ID to search for
I11 = Min Date (1 Jan 2018)
I12 = Max Date (5 jan 2018)

J10 = =MATCH($I$10,$A$1:$A$6,0) get the row number if the ID

=INDEX(INDEX($A$1:$G$6,$J$10,),
       MATCH(
             MAXIFS(INDEX($A$1:$G$6,$J$10,),
                    INDEX($A$1:$G$6,$J$10,),">="&$I$11,
                    INDEX($A$1:$G$6,$J$10,),"<="&$I$12
                   ),
             INDEX($A$1:$G$6,$J$10,),0
             ) - 1
      )

were
INDEX($A$1:$G$6,$J$10,)get the data row for the ID (repeated several time in formula)
MAXIFS(...) get the maximum date meeting the >= and <= criteria
MATCH(MAXIF(...), [ID data row],0) get the column number for the date returned by the MAXIF
INDEX( [ID data row], MATCH(MAXIF(...), [ID data row],0) - 1)returns the color

Alternative Formula without MAXIFS

=INDEX(INDEX($A$1:$G$6,$J$10,),
            MATCH(
                  MAX(IFERROR(({0,0,1,0,1,0,1})*(INDEX($A$1:$G$6,$J$10,)>=$I$11)*(INDEX($A$1:$G$6,$J$10,)<=$I$12)*(INDEX($A$1:$G$6,$J$10,)),0)),
            INDEX($A$1:$G$6,$J$10,),0
            ) - 1
      )

Enter as an Array Formula (complete with CtrlShiftEnter rather than just Enter)

edited Jun 25, 2018 at 1:45

answered Jun 25, 2018 at 0:27

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Inge Over a year ago

Thanks so much!! However, I am using Excel 2013 and not 2016 so MAXIFS is unfortunately not a functionality I have (yet). As my Excel is server driven I cannot upgrade it at the moment.

Solar Mike Over a year ago

@Inge You "accept" or say thanks by upvoting and clicking the arrow symbol - found top left of the answer...

Collectives™ on Stack Overflow

Excel: find value in column, then search for date value in that row, return value to the left

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related