CODEWITHSUNDEEP
excel
Matthew Sechrist
Matthew Sechrist

Reputation: 35

Compare 2 ranges of cells and return a value from a 3rd range of cells in excel

I create my work's employee schedule and I am having trouble. I have a year long spreadsheet where each column is one day. I have two rows that I need to compare values in up to a certain day (column). When the latest match is found, I want the cell to return the date from a third row corresponding to the column of the latest match. Here is what I have so far:

{=INDEX($L$6:$NM$6, MATCH("X"&"DTP",$L$3:$NM$3&$L8:$NM8,0))}

Where "X" is a conditional value, "DTP" is the job assignment, and columns L6-NM6 are the days of the year. However, this only returns the first instance that both conditions are true, not the last. So I tried:

{=INDEX($L$6:$NM$6, 1, MAX(IF($L$3:$NM$3="X"&$L8:$NM8="DTP",COLUMN($L$3:$NM$3)-COLUMN($L$3)+1&COLUMN($L8:$NM8)-COLUMN($L8)+1)))}

However, this only returned January 1, 2017.

Both of these attempts still don't include a date limit to give me the highest date before the next month's schedule.

If I try to include a date limit in the second attempt above, I put:

{=INDEX($L$6:$NM$6<$B$1.....(with all the rest the same)

Where B1 contains the date the function is to look up to and stop. With this, the overall function simply returns TRUE

Upvotes: 1

Views: 111

Answers (1)

A.S.H
A.S.H

Reputation: 29362

This array formula

=AGGREGATE(14,6,L6:NM6/((L3:NM3="X")*(L8:NM8="DTP")), 1)

Will fetch you the largest date from row6 where row3 is "X" and row8 is "DTP". It should work even if the days are not sorted. But since they are, it's expected to return the "rightmost" match.

The formula forces a div/0 in the cells that don't match the criteria, so that the parameter 6 (ignore errors) will drop them from the search for the max (param 14).

Upvotes: 1

Related Questions