Help with finding a formula (Lookup, Sum, and If all in one)

%3CLINGO-SUB%20id%3D%22lingo-sub-3407641%22%20slang%3D%22en-US%22%3EHelp%20with%20finding%20a%20formula%20(Lookup%2C%20Sum%2C%20and%20If%20all%20in%20one)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3407641%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20have%20a%20problem%20that%20I'm%20hoping%20some%20of%20you%20can%20help%20me%20solve.%3CBR%20%2F%3EI%20have%20two%20separate%20tables%20created%20in%20Excel.%3C%2FP%3E%3CP%3EThe%20first%20table%20named%20%3CEM%3E%5BROUTER_JOB_MERGED_InspectionLog%5D%20%3C%2FEM%3Eis%20a%20dashboard%20that%20displays%20Parent%20part%20numbers%20per%20column%20C%20%22ERGOSEAL%20PART%20No.%22%20and%20any%20corresponding%20sales%20orders%20that%20are%20open%2C%20past%20due%2C%20etc.%20etc.%20The%20last%20column%20(column%20Y)%20called%20%22BUILD%20STATUS%22%20is%20supposed%20to%20act%20as%20an%20indicator%20of%20whether%20or%20not%20all%20the%20required%20Child%20components%20that%20go%20into%20the%20Parent%20part%20per%20column%20C%20%22ERGOSEAL%20PART%20No.%20meet%20or%20exceed%20the%20quantity%20required%20for%20the%20sales%20order%20in%20column%20O%20%22QTY_ORDERED.%20Using%20the%20first%20part%20no.%20shown%20as%20an%20example%2C%20I%20need%20the%20''BUILD_STATUS%22%20column%20to%20display%20%22CAN%20BUILD%22%20%3CSTRONG%3Eonly%20if%20%3C%2FSTRONG%3Ethere%20are%2016%20or%20more%20%3CSTRONG%3Eof%20each%3C%2FSTRONG%3E%20of%20the%20component%20parts%20on%20hand%20that%20can%20be%20used%20to%20build%20the%20Parent%20part%20no.%2011-387-020%20as%20shown.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20then%20have%20a%20second%20table%20named%20%3CEM%3E%5BV_BOM_MSTR_InspectionLog%5D%20%3C%2FEM%3Ethat%20displays%20all%20the%20child%20components%20required%20for%20each%20parent%20part.%20The%20parent%20part%20no.%20is%20found%20in%20the%20column%20%22PARENT_ERGO_PART%20No.%22%20and%20the%20child%20part%20no.%20is%20found%20in%20the%20column%20%22CHILD_ERGO_PART%20No.%22%20The%20on%20hand%20quantity%20for%20each%20child%20part%20no.%20is%20shown%20in%20column%20%22CHILD_QTY_ONHAND%22.%3C%2FP%3E%3CP%3ECurrently%2C%20the%20formula%20I'm%20using%20to%20populate%20the%20%22BUILD_STATUS%22%20column%20in%20the%20first%20table%20is%3C%2FP%3E%3CP%3E%3CEM%3E%3DIF(XLOOKUP(%5B%40%5BERGOSEAL%20PART%20No.%5D%5D%2CV_BOM_MSTR_InspectionLog%5BPARENT_ERGO_PART%20No.%5D%2CV_BOM_MSTR_InspectionLog%5BCHILD_QTY_ONHAND%5D)%26gt%3B%3D%5B%40%5BQTY_ORDERED%5D%5D%2C%22CAN%20BUILD%22%2C%22SHORT%22)%3C%2FEM%3E%3C%2FP%3E%3CP%3EThis%20formula%20works%20in%20the%20sense%20that%20it%20does%20provide%20a%20%22CAN%20BUILD%22%20status%20but%20it%20fails%20in%20doing%20everything%20I%20need.%20Since%20its%20a%20lookup%20formula%2C%20its%20returning%20a%20%22CAN%20BUILD%22%20or%20%22SHORT%22%20result%20if%20the%20first%20component%20it%20finds%20meets%20the%20criteria.%20It%20doesn't%20work%20if%20the%20next%20component%20or%20any%20of%20the%20remaining%20components%20have%20less%20than%20the%20required%20quantity%20available.%20As%20you%20can%20see%20in%20the%20sample%20data%20that%20I've%20attached%20%2C%20the%20result%20that%20should%20be%20returned%20in%20the%20first%20table%20%22BUILD%20STATUS%22%20column%20is%20%22SHORT%22%20because%20there%20is%20one%20child%20component%20(CO-242-060-042-000)%20in%20the%20second%20table%20that%20has%20%220%22%20on%20hand.%20Is%20there%20a%20formula%20that%20can%20do%20what%20I'm%20trying%20to%20accomplish%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%40%3CA%20title%3D%22Excel%20Data%22%20href%3D%22https%3A%2F%2Fergoseal.sharepoint.com%2F%3Ax%3A%2Fs%2FErgosealInc%2FEYj1MWT8F_tHhaTVGwbtrmcBIbesKA_cPgxOQTyKjd5ZqQ%3Fe%3D8cxgb2%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20Tables%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3407641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

So I have a problem that I'm hoping some of you can help me solve.
I have two separate tables created in Excel.

The first table named [ROUTER_JOB_MERGED_InspectionLog] is a dashboard that displays Parent part numbers per column C "ERGOSEAL PART No." and any corresponding sales orders that are open, past due, etc. etc. The last column (column Y) called "BUILD STATUS" is supposed to act as an indicator of whether or not all the required Child components that go into the Parent part per column C "ERGOSEAL PART No. meet or exceed the quantity required for the sales order in column O "QTY_ORDERED. Using the first part no. shown as an example, I need the ''BUILD_STATUS" column to display "CAN BUILD" only if there are 16 or more of each of the component parts on hand that can be used to build the Parent part no. 11-387-020 as shown.

I then have a second table named [V_BOM_MSTR_InspectionLog] that displays all the child components required for each parent part. The parent part no. is found in the column "PARENT_ERGO_PART No." and the child part no. is found in the column "CHILD_ERGO_PART No." The on hand quantity for each child part no. is shown in column "CHILD_QTY_ONHAND".

Currently, the formula I'm using to populate the "BUILD_STATUS" column in the first table is

=IF(XLOOKUP([@[ERGOSEAL PART No.]],V_BOM_MSTR_InspectionLog[PARENT_ERGO_PART No.],V_BOM_MSTR_InspectionLog[CHILD_QTY_ONHAND])>=[@[QTY_ORDERED]],"CAN BUILD","SHORT")

This formula works in the sense that it does provide a "CAN BUILD" status but it fails in doing everything I need. Since its a lookup formula, its returning a "CAN BUILD" or "SHORT" result if the first component it finds meets the criteria. It doesn't work if the next component or any of the remaining components have less than the required quantity available. As you can see in the sample data that I've attached , the result that should be returned in the first table "BUILD STATUS" column is "SHORT" because there is one child component (CO-242-060-042-000) in the second table that has "0" on hand. Is there a formula that can do what I'm trying to accomplish?

@Excel Tables 

0 Replies