0

I am trying to fill a table using Index Match Match, which returns me the correct values for the top row, however i get a #Ref! error when I try to drag down the formula vertically. I am using this formula:

INDEX($C$3:$C$44,MATCH($K$15,$A$3:$A$44,0),MATCH(H16,$B$3:$B$44,0))

(See picture for further details on table) enter image description here

Any idea where my mistake lies?

  • 1
    What's the actual contents of the cell: M25? – RayInNoIL Oct 15 '18 at 14:57
  • When you move to column M, it's still looking at $K$15 for the Quarter. Perhaps there is no Q2 for that specific row? Maybe try just =INDEX($C$3:$C$44,MATCH(K$15,$A$3:$A$44,0),MATCH($H16,$B$3:$B$44,0)) (also anchor that H16 reference to the column) – BruceWayne Oct 15 '18 at 14:59
  • 1
    the second match is giving how many columns to move over, yet all your years are in a single column – Forward Ed Oct 15 '18 at 15:01
2

Pay attention to cell reference locks $, and you can place the following formula under Q1 and beside your first row and copy down and to the right.

=INDEX($C:$C,AGGREGATE(15,6,ROW($A$3:$A$44)/(($A$3:$A$44=H$8)*($B$3:$B$44=$F9)),1))

POC

I believe the problem you are having is that you are trying to return the value in column C that matches a criteria for column A and for column B. In other words INDEX(Column you want the results from, The row of your result) Note there is only 1 entry or coma after specifying the the column you will return the results from. A second column would tell index how many columns to the right to go.

PS

AGGREGATE is a special function. It will perform array like calculations. Therefore avoid using full column references inside the aggregate function.

0

You might want to tweak how it's done, enter this as an Array Formula (with CTRL+SHIFT+ENTER

=INDEX($C$3:$C$44,MATCH($K$15&H16,$A$3:$A$44&$B$3:$B$44,0))

And drag down.

Edit: Just made a comment, but you might alternatively just need to properly anchor the references:

=INDEX($C$3:$C$44,MATCH(K$15,$A$3:$A$44,0),MATCH($H16,$B$3:$B$44,0))
  • Bruce, in reference to your second formula...INDEX(RANGE,ROWS DOWN, COLUMN ACROSS). How do you intend to move across columns when you have a 1 dimensional range? now if you had a two dimensional range I would totally buy what you are saying. – Forward Ed Oct 15 '18 at 15:14
  • @ForwardEd - Do you mean with the second formula? I agree actually, after thinking through I think that second "column across" is incorrect. My first reply should work though. (Non-excuse, I haven't had coffee yet :/ ) – BruceWayne Oct 15 '18 at 15:17
  • yeah I editted my first comment after looking at your first formula and seeing it only had a single match...was not sure how it was going to work, but agreed that it was only going to give 1 number. – Forward Ed Oct 15 '18 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.