-1

I was trying to use the INDEX and MATCH function thus:

=INDEX(G2,I2,K2,S2:T2,Y2:AB2,0,MATCH(MAX(G3,I3,K3,S3:T3,Y3:AB3),G3,I3,K3,S3:T3,Y3:AB3,0))

Then I got the error message: You have entered too many arguments for this function

Is there any way to go around this?

  • 1
    INDEX() takes up to 4 arguments - you have given it 7. `MATCH() takes up to 3 arguments - you have given it 7. It is impossible to work out what you are trying to achieve, so I cannot suggest a correction. – AFH Jun 12 '18 at 14:16
0

Your formula is wrong, because it has indeed to many arguments.


The INDEX function works like this:

=INDEX(<area where you're searching something>, <row number>, [<column number>])

Therefore:

  • The 1st argument must be a range, for instance G5:L10.
  • The 2nd argument must be the row number that you want to return in the range. With the range G5:L10 and the argument 2, you will return the 2nd row of that range, i.e. 6:6.
  • The 3rd argument is the same as the 2nd, but refers to the column number. It is optional.

Check Excel's documentation for more information.


The MATCH function is used here because we don't really know which line/column number to use in the INDEX. However, tables often have a header with a human-readable name, or a first cell containing an ID, a date, a name, etc. It would be great to ask Excel: "give me the number of the row that has the date 2018-05-24 inside", instead of "use row number 634".

Luckily, the function MATCH(<what>,<where>,<how>) can do that. It simply returns the position of what you're searching, within the array of values (where). The how lets you configure how the function behaves (cf. Excel's documentation).

In other words, if your range contains:

1  Apple
2  Pear
3  Banana
4  Orange

The formula MATCH("Banana", A1:A4, 0) will return 2.


By combining INDEX and MATCH, you can ask Excel to give you the intersection of a row and a column, within a given area of possible values.

I suggest you to consult this article about how to use INDEXandMATCH`:

Excel formula: Two-way lookup with INDEX and MATCH on ExcelJet

0

It looks like you are trying to find the value in the cell above the cell that has the maximum value in a set of discontinuous ranges in the same row.

The actual solution to that, given your set of ranges, is:

=INDEX(2:2,MATCH(MAX(G3,I3,K3,S3:T3,Y3:AB3),3:3,0))

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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