2

I have an Excel sheet with the following data:

-----------------------------------------
| Unique line ID  | ID | Status         |
|---------------------------------------|
| 1               | AA | active         |
| 2               | AA | upgraded       |
| 3               | AA | out of service |
| 4               | AB | ...            |
-----------------------------------------

And I have another table where I have only the ID, and I need to match the Status, but I want to prioritize it and have the latest one, which is the biggest one ordered by Unique line ID.

So in this example for AA, I need to match the status out of service.

3
  • 1
    What did you try?
    – Dave
    Sep 29 '16 at 9:58
  • 1
    Is Unique Line ID always in ascending order in your sheet?
    – patkim
    Sep 29 '16 at 10:02
  • Not necessarily, but the latest status has always the biggest Unique line ID Sep 30 '16 at 11:42
1

Use INDEX/MATCH with Aggregate to get the max Unique Id:

=INDEX($C$2:$C$5,MATCH(AGGREGATE(14,6,$A$2:$A$5/($B$2:$B$5=E2),1),$A$2:$A$5,0))

enter image description here

0

May be something like this It will find the last value (Your data should be sorted on IDenter image description here

Thanks

0

If you want to keep your formulas short and easily understandable:

Step 1:

Add a column next to the ID column that combines the Unique line ID and the ID column. If the data is as per sheet below, on cell C2, enter the formula =A2&B2 and drag it down. This column can later be hidden.

step1

Step 2:

On the 2nd table, on the first status cell, enter the following array formula and drag it down as needed.

=VLOOKUP(MAX(IF(B:B=F2,A:A))&F2,C:D,2,FALSE)

Note: When you enter this in the first cell, instead of pressing Enter press CTRL+SHIFT+Enter since its an array formula. { and } will appear as in the screenshot after you do this.

step2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.