0

I am receiving the REF! error in excel when trying to use the index double match.

Can anyone tell me where i am going wrong. Thank you

Placed in cell A1:

DATE    Product     VALUE
1   TEST A   10,724.00 
1   TEST B   15,033.00 
1   TEST C   4,897.00 
1   TEST D   36,241.00 
1   TEST E   8,182.00 
2   TEST A   1,457.00 
2   TEST B   20,927.00 
2   TEST C   5,304.00 
2   TEST D   43,114.00 
2   TEST E   27,002.00 
3   TEST A   30,492.00 
3   TEST B   29,073.00 
3   TEST C   19,570.00 
3   TEST D   36,535.00 
3   TEST E   39,080.00

Table i am populating:

    1   2   3
TEST A   10,724.00  #REF!   #REF!
TEST B   15,033.00  #REF!   #REF!
TEST C   4,897.00   #REF!   #REF!
TEST D   36,241.00  #REF!   #REF!
TEST E   8,182.00   #REF!   #REF!

When populating the '1' - it seems to populate correctly. Formula used:

=INDEX($C$2:$C$16,MATCH(F2,$B$2:$B$16,0),MATCH($G$1,$A$2:$A$16,0))

However, when i try to use the formula for 2 and 3, i receive the REF error. (Trying to populate the results for '2')

=INDEX($C$2:$C$16,MATCH(F2,$B$2:$B$16,0),MATCH($H$1,$A$2:$A$16,0))

Thank you in advanced

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
kirstysmith589
  • 1
  • 2
  • The third criteria of INDEX is the column reference not a secondary row reference. – Scott Craner Sep 11 '19 at 19:46
  • = `INDEX($C$2:$C$16,,)` necessitates that `y` must be 1, since that is the width of the array being looked up (`$C$2:$C$16`). Probably `y` in your formula above is returning a number higher than 1, thus resulting in a `#REF` error, since you are outside the bounds of the lookup array. – ImaginaryHuman072889 Sep 11 '19 at 19:47
  • See Here for many different ways to do a dual lookup: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Sep 11 '19 at 20:08

1 Answers1

1

The third criteria of INDEX is the column reference not a secondary row reference.

I would use SUMIFS():

=SUMIFS($C:$C,$A:$A,G$1,$B:$B,$F2)

Copy over and down.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81