CODEWITHSUNDEEP
excelindexingmatchvlookup
heyhey33
heyhey33

Reputation: 29

Excel getting max from multiple rows in second table

I have two tables

First table:

ML_ID   PID           Task Name Start   Color
1       PID1      One       01.01.2016  R
2       PID2,PID3     Two       #N/A    G
3       PID3      Three     01.03.2016  A
4       PID4      Four      01.04.2016  R
5       PID5      Five      01.05.2016  G
6       PID6      Six       01.06.2016  A
7       PID7      Seven     01.07.2016  R
8       PID8      Eight     01.08.2016  G
9       PID9      Nine      01.10.2016  A
10      PID10     Ten       01.09.2016  R

Second table

PID         Dates
PID1    01.01.2016
PID2    01.02.2016
PID3    01.03.2016
PID4    01.04.2016
PID5    01.05.2016
PID6    01.06.2016
PID7    01.07.2016
PID8    01.08.2016
PID10   01.09.2016
PID9    01.10.2016

For ML_ID 2 I want to find the max date for PID2 and PID2 with a lookup on the second table.

I made several attempts with INDIRECT(INDEX(MATCH())) combined with MAX(IF()).

I can still influence the data structure, if there is a better way. However, on the first table I can only have one row per ML_ID!

I guess the tricky part is, that I need either one or several lookups - depending on the number of PIDs in the first table.

Any thoughts on that?

Upvotes: 1

Views: 90

Answers (1)

xidgel
xidgel

Reputation: 3145

AMENDED TO FIX A BUG. Thanks Adam! This might work for you. Start with this:

=IF(ISERROR(FIND(Table2_PIDs&",","PID2,PID3"&",")),0,Table2_Dates)

entered as an array formula (CTRL-SHIFT-ENTER). [Note: the &"," forces FIND to find exact matches --- searching for "PID1," won't mistakenly find "PID10".] This tests the members of Table2_PIDs one at a time to see if they occur in the string "PID2,PID3,". If NOT found, the FIND function will return #VALUE, which ISERROR will catch and cause the IF to return a zero. If FOUND, ISERROR will return False, so the result of the IF statement will be the corresponding member of Table2_Dates. So the function will return an array like {0;01.02.2016;01.03.2016;0;0;0;0;0;0;0}.

Now wrap the previous formula with a MAX to return the latest date of any of the matching PIDs:

=MAX(IF(ISERROR(FIND(Table2_PIDs&",","PID2,PID3"&",")),0,Table2_Dates))

again entered as an array formula.

The final step is the connect this to the ML_ID, which we do by replacing the string literal "PID2,PID3" with the results of a VLOOKUP of ML_ID in Table1:

=MAX(IF(ISERROR(FIND(Table2_PIDs&",",VLOOKUP(ML_ID,Table1,2,FALSE)&",")),0,Table2_Dates))

entered as an array formula. Hope that helps.

Upvotes: 1

Related Questions