May 10 2021 12:07 PM
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)
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)
The highlighted in green shows where the formula has functioned correctly.
Please assist!
May 10 2021 12:20 PM
Solution=INDEX(W324:W337,MATCH(1,INDEX((S324:S337=D324)*(T324:T337=E324)*(U324:U337=F324)*(V324:V337=G324),,),0))
May 10 2021 12:37 PM
May 10 2021 12:38 PM
That could be like
=INDEX(W324:W337,
MATCH(1, INDEX(
(D324=S324:S337)*
(E324=T324:T337)*
(F324=U324:U337)*
(G324=V324:V337)
,0), 0)
)
May 10 2021 12:20 PM
Solution=INDEX(W324:W337,MATCH(1,INDEX((S324:S337=D324)*(T324:T337=E324)*(U324:U337=F324)*(V324:V337=G324),,),0))