 SOLVED

# Index Match with 4 Variables

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. 3 Replies
best response confirmed by fisherayda (New Contributor)
Solution

# Re: Index Match with 4 Variables

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

# Re: Index Match with 4 Variables

It works perfectly! Thank you so much! # Re: Index Match with 4 Variables

That could be like

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