CODEWITHSUNDEEP
excel
user2861089
user2861089

Reputation: 1245

Excel function for indexing and matching multiple criteria

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!

Upvotes: 0

Views: 70

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

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

Upvotes: 1

Related Questions