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
jpm_365
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
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)

@Twifoo 

Thank you so much!

You’re very much welcome!
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies