SOLVED

Index Match with 4 Variables

%3CLINGO-SUB%20id%3D%22lingo-sub-2342310%22%20slang%3D%22en-US%22%3EIndex%20Match%20with%204%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342310%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20Windows%20user%2C%20using%20Version%202104%20of%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20day%20I%20receive%20a%20spreadsheet%20containing%20four%20variables%20with%20the%20total%20number%20of%20items%20the%20customer%20would%20like%20for%20that%20day.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attempting%20to%20create%20a%20formula%20where%20I%20could%20copy%2Fpaste%20the%20raw%20data%20(right)%20into%20a%20premade%20table%20(left)%20in%20excel%2C%20as%20there%20are%20almost%20800%20total%20combinations%20and%20combing%20through%20each%20one%20can%20be%20quite%20time%20consuming.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20able%20to%20make%20the%20formula%20function%20for%20all%20numerical%20variables%20(gauge%20(decimal))%2C%20width%2C%20and%20length)%2C%20but%20have%20received%202%20types%20of%20errors%2C%20and%20had%20have%20no%20success%20when%20introducing%20an%20alphabetical%2Ftext%20value%20(grade%20noted%20as%201%2F4%20Steel%2C%207%20GA%20steel%2C%20etc).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20error%20is%20that%20the%20formula%20believes%20there%20is%20a%20request%20for%201%2F4%20Steel%20x%20.25%20x%2060%20x%2032%20when%20there%20is%20none.%26nbsp%3B%20(purple%20highlighted)%3C%2FP%3E%3CUL%3E%3CLI%3E%3DINDEX(W324%3AW337%2CMATCH(D324%2CS324%3AS337%2C0)*MATCH(E324%2CT324%3AT337%2C0)*MATCH(F324%2CU324%3AU337%2C0)*MATCH(G324%2CV324%3AV337%2C0)%2C0)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20second%20error%20is%20that%20the%20formula%20believes%20there%20is%20a%20request%20for%201%2F4%20%3CSTRONG%3ESteel%3C%2FSTRONG%3E%20x%20.25%20x%2060%20x%2062%2C%20but%20it%20is%20actually%20for%26nbsp%3B1%2F4%20%3CSTRONG%3EDomex%3C%2FSTRONG%3E%20x%20.25%20x%2060%20x%2062.%20(red%20highlighted)%3C%2FP%3E%3CUL%3E%3CLI%3E%3DINDEX(W324%3AW337%2CMATCH(D342%2CS324%3AS337%2C0)*MATCH(E342%2CT324%3AT337%2C0)*MATCH(F342%2CU324%3AU337%2C0)*MATCH(G342%2CV324%3AV337%2C0)%2C0)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20highlighted%20in%20green%20shows%20where%20the%20formula%20has%20functioned%20correctly.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22fisherayda_1-1620673588652.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279608i5A876BC51CB00358%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22fisherayda_1-1620673588652.png%22%20alt%3D%22fisherayda_1-1620673588652.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20assist!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2342310%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2342333%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20with%204%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1050115%22%20target%3D%22_blank%22%3E%40fisherayda%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(W324%3AW337%2CMATCH(1%2CINDEX((S324%3AS337%3DD324)*(T324%3AT337%3DE324)*(U324%3AU337%3DF324)*(V324%3AV337%3DG324)%2C%2C)%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2342361%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20with%204%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2342361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20perfectly!%20Thank%20you%20so%20much!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22fisherayda_0-1620675433883.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279613i6C8C4212C6332392%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22fisherayda_0-1620675433883.png%22%20alt%3D%22fisherayda_0-1620675433883.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

 

I am a Windows user, using Version 2104 of excel. 

 

Each day I receive a spreadsheet containing four variables with the total number of items the customer would like for that day. 

 

I am attempting to create a formula where I could copy/paste the raw data (right) into a premade table (left) in excel, as there are almost 800 total combinations and combing through each one can be quite time consuming. 

 

I have been able to make the formula function for all numerical variables (gauge (decimal)), width, and length), but have received 2 types of errors, and had have no success when introducing an alphabetical/text value (grade noted as 1/4 Steel, 7 GA steel, etc). 

 

The first error is that the formula believes there is a request for 1/4 Steel x .25 x 60 x 32 when there is none.  (purple highlighted)

  • =INDEX(W324:W337,MATCH(D324,S324:S337,0)*MATCH(E324,T324:T337,0)*MATCH(F324,U324:U337,0)*MATCH(G324,V324:V337,0),0)

The second error is that the formula believes there is a request for 1/4 Steel x .25 x 60 x 62, but it is actually for 1/4 Domex x .25 x 60 x 62. (red highlighted)

  • =INDEX(W324:W337,MATCH(D342,S324:S337,0)*MATCH(E342,T324:T337,0)*MATCH(F342,U324:U337,0)*MATCH(G342,V324:V337,0),0)

The highlighted in green shows where the formula has functioned correctly. 

fisherayda_1-1620673588652.png

 

Please assist! 

 

 

 

3 Replies
best response confirmed by fisherayda (New Contributor)
Solution

@fisherayda 

=INDEX(W324:W337,MATCH(1,INDEX((S324:S337=D324)*(T324:T337=E324)*(U324:U337=F324)*(V324:V337=G324),,),0))

@Hans Vogelaar 

 

It works perfectly! Thank you so much! 

 

fisherayda_0-1620675433883.png

 

@fisherayda 

That could be like

=INDEX(W324:W337,
   MATCH(1, INDEX(
      (D324=S324:S337)*
      (E324=T324:T337)*
      (F324=U324:U337)*
      (G324=V324:V337)
       ,0), 0)
)