Index Match with 4 Variables

[fisherayda]

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. 

Please assist! 

[#2 · Accepted solution]

@fisherayda 

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

[#3]

@Hans Vogelaar 

It works perfectly! Thank you so much! 

[#4]

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