SOLVED

Multilayered Lookup

%3CLINGO-SUB%20id%3D%22lingo-sub-1781363%22%20slang%3D%22en-US%22%3EMultilayered%20Lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781363%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20set%20up%20a%20workbook%20that%20pulls%20data%20from%20another%20sheet%20with%20multiple%20criteria.%20The%20sheet%20has%20a%20list%20of%20production%20steps%20for%20various%20orders%2C%20along%20with%20the%20number%20of%20that%20step%20in%20the%20process%2C%20whether%20the%20step%20is%20active%20or%20not%2C%20and%20how%20many%20of%20the%20product%20have%20been%20produced%20to%20this%20point.%20I%20am%20trying%20to%20figure%20out%20how%20to%20pull%20the%20number%20produced%20from%20the%20line%20on%20the%20sheet%20with%20the%20most%20recent%20step%20that%20is%20actually%20active.%20In%20other%20words%2C%20for%20each%20production%20order%2C%20I%20need%20to%20find%20which%20row%20has%20the%20highest%20numbered%20operation%20that%20is%20marked%20as%20active%2C%20then%20pull%20the%20completed%20product%20number%20from%20that%20row.%20Is%20there%20a%20formula%20combination%20that%20can%20achieve%20this%3F%20I%20have%20attached%20a%20simplified%20example%20mockup%20to%20illustrate%20the%20situation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1781363%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1781477%22%20slang%3D%22en-US%22%3ERe%3A%20Multilayered%20Lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F832748%22%20target%3D%22_blank%22%3E%40SamSuchanek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to set up a workbook that pulls data from another sheet with multiple criteria. The sheet has a list of production steps for various orders, along with the number of that step in the process, whether the step is active or not, and how many of the product have been produced to this point. I am trying to figure out how to pull the number produced from the line on the sheet with the most recent step that is actually active. In other words, for each production order, I need to find which row has the highest numbered operation that is marked as active, then pull the completed product number from that row. Is there a formula combination that can achieve this? I have attached a simplified example mockup to illustrate the situation.

2 Replies
Highlighted
Best Response confirmed by SamSuchanek (New Contributor)
Solution

@SamSuchanek 

See the attached version.

Highlighted

@Hans Vogelaar Thank you! I had to modify the formulas slightly since I am using an older version of excel but this solution worked perfectly!