0

Say I have the following column headings filled with data on Sheet2:

| Station number | Year | Month| Rainfall (mm) |
       XX1         1995    1       30
       XX1         2005    4       50
       XX1         2004    5       70

...

And the following columns on Sheet1:

|Year | Month of Max | mm | Month of Min | mm |
 2004     
 2005
 2006

...

I have the following code under the "Month of Max" column:

=INDEX(Sheet2!B2:B10000,MATCH(MAX(Sheet2!D2:D10000),Sheet2!D2:D10000,0))

However, I want to match the years too (i.e. the year in Sheet1!A2 should match the year in Sheet2!B2:B10000 for looking up the maximum rainfall). I can't figure out where to put this function and keep getting errors. Sorry if this is obvious or has been asked before, I'm just very stuck!

1 Answer 1

1

MATCH is not suited to matching multiple creiteria. An alternative is this

=MAX(IF(Sheet2!$B$2:$B$10000=Sheet1!A2,Sheet2!$D$2:$D$10000,""))

enter as an Array Formula (press Ctrl-Shift-Enter rather than just Enter)

The IF part returns an array, with entries for non-matching years set to a null String. MAX then gets the maximum value of that array

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

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.