mardi 5 mai 2020

Excel MAX and IF with multiple conditions

Got transaction numbers in column A (no duplicates) and persons (numbers) in column E (duplicates may occur). To find the highest number in A for each person in E, and mark it out as "Last event" or "other" in each row, I've done this in column Z:

{=IF(A2=MAX(IF(E2=$E$2:$E$50000,$A$2:$A$50000,"")),"Last event","other")}

Formula works, and points out "Last event" in only 1 row (for each person number), and "other" in all other rows holding the same person number.

But now I need to add another column in my search. Column S need to be added as a condition when cell value in S is "JobList" (duplicates may occur). Result should be the same: "Last event" in only 1 row for each person number + JobList, and "other" in all other rows.

I've been struggeling all day about this, but can't figure out how to do it. Any suggestions?

Aucun commentaire:

Enregistrer un commentaire