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!