Search code examples
excelexcel-formulaexcel-2019

How do I create column that return the value of other column based on rank from another column?

enter image description here

For one ID, there are several occurrences as shown in column Count. Each occurrence has its own value and status.

I want to create column that will return last occurrence's status for all occurrences in one ID.

For example, ID "456" has 4 occurrences. On the last one, occurrence 4 (row 9), the Status is "not ok". So on the Result column, every occurrence for ID "456" will show "not ok" (row 4, 7, 8, 9)

Solution

  • You could try using the LOOKUP() function, as it seems it would be suffice here:

    enter image description here

    =LOOKUP(2,1/(B2=$B$2:$B$10),$E$2:$E$10)