1

I am creating a matrix of sorts, where column A has a list of parts and the Row 1 is populated with weeks of the year in yyyy/mm format. What I am trying to do is populate this matrix with quantity data from an aggregate sheet, where the demand quantities for each part is listed by week of the year. In this aggregate sheet, column A lists the parts, column B lists the weeks, and column C lists the quantities.

I've been trying to write an =INDEX(MATCH(),MATCH()) type of formula in order to fetch the value of column C if the values from Columns A & B match the values in Column A and Row 1 respectively on the matrix sheet, but have been only getting #REF errors in return. At this point I need a second pair of eyes. Here is the formula:

=INDEX(MRP!$C$1:$C$6400,MATCH(A2,MRP!$A$2:$A$6400,0),MATCH(B1,MRP!$B$2:$B$6400,0))

Am I going about this the right way, or is another method needed instead?

Improve this question

3 Answers 3

Reset to default
1

Cell B2 =SUMIFS(MRP!$C:$C,MRP!$A:$A,Sheet1!$A3,MRP!$B:$B,Sheet1!B$1)

You can then copy these down and across.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You are getting the #REF error because you are out of range of the array you are trying to match. The index function takes two arguments and one optional argument. 

INDEX(array, row_num, [column_num])

Looking at your formula, 

=INDEX(MRP!$C$1:$C$6400,MATCH(A2,MRP!$A$2:$A$6400,0),MATCH(B1,MRP!$B$2:$B$6400,0))
  • MRP!$C$1:$C$6400 is the array to be matched
  • MATCH(A2,MRP!$A$2:$A$6400,0) gets the row number. Maybe it should be MATCH(A1....)?
  • MATCH(B1,MRP!$B$2:$B$6400,0) gets the column number.

But since your matching array only have one column, the column number from the match function is greater than 1, which is outside the range of the array.

If you need to match both values in column A and B, you can use this formula to match

=MATCH(lookup_value_1 & lookup_value_2, lookup_array_1 & lookup_array_2, match_type)

For you case, it would be 

=MATCH(A1 & B1, MRP!$A$2:$A$6400 & MRP!$B$2:$B$6400, 0)

you'll need to commit your formula using Ctrl+Shift+Enter rather than just pressing Enter. This will get you the row number where both col A and B matches cell A1 and B1.

Finally, you can index it

=INDEX(MRP!$C$1:$C$6400,Result from above match,0)
Improve this answer

4 Comments

This feels close, although I'm still getting a #NA error with the first step of the formula you recommended (even after committing with Ctrl+Shift+Enter). Breaking the formula down to just INDEX(MATCH()), I get this: =INDEX(MRP!C2:C6400,MATCH(A2,MRP!A2:A6400,0)) returns the first quantity value listed for that part. =INDEX(MRP!C2:C6400,MATCH(B1,MRP!B2:B6400,0)) returns the first quantity value listed for that yyyy/ww date. The ultimate goal is to refine the search so I get the specific quantity for that part on that specific yyyy/ww date.
try this line =MATCH(A1 & B1, MRP!$A$2:$A$6400 & MRP!$B$2:$B$6400, 0). It is meant to get exactly what you are looking for, the line that matches both criteria.
I'm still getting a #VALUE error (which turns into #N/A with Ctrl+Shift+Enter).
=INDEX(MRP!$C$1:$C$6400,MATCH(A1 & B1, MRP!$A$2:$A$6400 & MRP!$B$2:$B$6400, 0),0) is that what you did? Do you see the {} around this code after you do Ctrl+Shift+Enter? Make sure your data is already present in those columns.
0

I would use SUMIFS. Create a helper column in your second sheet with the month number in and then use

=SUMIFS(sheet2!column C, sheet 2!helper column, month(sheet1!A$1),sheet2! column A, sheet1!$A1)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.