Home

Finding the last instance of a data combination

%3CLINGO-SUB%20id%3D%22lingo-sub-643676%22%20slang%3D%22en-US%22%3EFinding%20the%20last%20instance%20of%20a%20data%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643676%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20index%20match%20based%20on%20the%20last%20instance%20of%20a%20combination%20of%20data%20elements.%26nbsp%3B%20In%20the%20attached%20example%2C%20I%20want%20to%20match%20the%26nbsp%3BUnits%20(col%20D)%20for%20the%20last%20instance%20of%20each%20combo%20of%20Name%20(col%20B)%20and%20Stock%20(col%20C).%26nbsp%3B%20This%20table%20is%20small%2C%20the%20real%20one%20is%20thousands%20of%20combinations.%26nbsp%3B%20I%20haven't%20had%20any%20luck%20figuring%20this%20one%20out%20on%20my%20own!%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643676%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644013%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20last%20instance%20of%20a%20data%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644013%22%20slang%3D%22en-US%22%3EYou%20may%20try%20this%20formula%20in%20I2%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DLOOKUP(PI()%2C%3CBR%20%2F%3E1%2F((B%242%3AB%24100%3DG2)*%3CBR%20%2F%3E(C%242%3AC%24100%3DH2))%2C%3CBR%20%2F%3ED%242%3AD%24100)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644708%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20last%20instance%20of%20a%20data%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644728%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20last%20instance%20of%20a%20data%20combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644728%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I need to index match based on the last instance of a combination of data elements.  In the attached example, I want to match the Units (col D) for the last instance of each combo of Name (col B) and Stock (col C).  This table is small, the real one is thousands of combinations.  I haven't had any luck figuring this one out on my own!  Thank you!

3 Replies
Highlighted
You may try this formula in I2, copied down rows:
=LOOKUP(PI(),
1/((B$2:B$100=G2)*
(C$2:C$100=H2)),
D$2:D$100)
Highlighted

@Twifoo 

Thank you so much!

Highlighted
You’re very much welcome!
Related Conversations
What data does Microsoft collect?
Tim_Gent in Microsoft Teams on
1 Replies
History and Data
rosenbloomsnka in Microsoft To Do on
1 Replies
AD User Migration Exchange Mailbox last
Hermulus in Exchange on
1 Replies