Help with using Index/Match alongside MAXIFS to get corresponding cells

[grld]

Hi all!

Having trouble with this one. I have the 2 separate pieces working, but I can't seem to figure out how to put them together.

Okay, so basically I am trying to format a record book that uses data from another sheet. Here is a screenshot of what it will look like:

Screen Shot 2019-07-02 at 11.44.26 AM.png

So, I have figured out how to get the number, which includes multiple criteria. The formula for that looks like this:

Please Login or Register  to view this content.

Next, I want to get the corresponding name, and week #, which are all on the same row as the previous number. I have not been able to figure out how to do that. I have this:

Please Login or Register  to view this content.

Which sort of works, but it only gets the max, and does not include the criteria ($AB and $AC) that I need filtered. I'm unsure of how to add those criteria.

Lastly, and this isn't the hugest deal in the world, but it would be nice, is I know that this will only return the first value if there are duplicates. Is there a way to print each value of duplicates?

For example, something like this:
Screen Shot 2019-07-02 at 11.52.09 AM.png

Thanks so much!

[jeppenedved]

Hi!
Feels like a combination of INDEX and MATCH should work, or OFFSET. Would you be able to upload the workbook? Perhaps only an example, which would make things easier to get a grip on.

[grld]

Thanks for the reply! I've uploaded a simple sample that shows what I am trying to do.

Basically in sheet '2007' I have a list of records. In the 'Season Records' sheet, I am pulling the max value from each column in '2007', where "Days" = (Col A12) and "Finals" = (Col A14).

I *then* want to pull the row's corresponding values for Column A ("Week") and Column B ("Team"), ideally pulling duplicates as well, though if that is too difficult, the first would suffice.

[grld]

gahhh, still stuck on this. anyone?

[CARACALLA]

Season Records


D5=IFERROR(INDEX('2007'!$B$2:$B$7&" week"&'2007'!$A$2:$A$7,SMALL(IF('2007'!C$2:C$7='Season Records'!D$4,ROW('2007'!$B$2:$B$7)-ROW('2007'!$B$2)+1),ROWS('2007'!$B$2:'2007'!$B2))),"")


control+shift+enter copy across and down

[FlameRetired]

Interpreted a little differently. Array enter in D5, fill down and across.

Please Login or Register  to view this content.

	A	B	C	D	E	F
1				Hits	AB	Runs
2
3
4	2007			63	209	40
5				John Doe	John Doe	John Doe
6						John Doe 3 Week 9
7
8

[grld]

Thanks! I got this one to work:

Please Login or Register  to view this content.

Curious, how do I add a condition to this? It's getting so long that I'm getting a bit confused as to where to put it. Let's say I want to add $AB$87="yes"
Where would that go?

Thanks again

[CARACALLA]

I change formula in post #5

new formula

D5=IFERROR(INDEX('2007'!$B$2:$B$7&" week"&'2007'!$A$2:$A$7,SMALL(IF('2007'!C$2:C$7='Season Records'!D$4,ROW('2007'!$B$2:$B$7)-ROW('2007'!$B$2)+1),ROWS('2007'!$B$2:'2007'!$B2))),"")

control +shift +enter

copy across and down

[grld]

Awesome, thank you so much. Still wondering about my followup question as well:

Curious, how do I add a condition to this? It's getting so long that I'm getting a bit confused as to where to put it. Let's say I want to add $AB$87="yes"
Where would that go?

[CARACALLA]

Sorry

I do not understand. $AB$87="yes" ?

Can you provide some sample data and what the expected outcome is?

[grld]

Sure, in this screenshot it would be $A$14

Attachment 630902

Where I would want to ignore all rows where the value in G2:G7 = "yes"

I've attached a workbook as well.

[grld]

Basically, just wondering where I would add a criteria to the formula so that I could ignore if it equals a specific value

[CARACALLA]

D5=IFERROR(INDEX('2007'!$B$2:$B$7&" week"&'2007'!$A$2:$A$7,SMALL(IF('2007'!E$2:E$7='Season Records'!F$4,IF('2007'!$G$2:$G$7<>'2007'!$G$5,ROW('2007'!$B$2:$B$7)-ROW('2007'!$B$2)+1)),ROWS('2007'!$B$2:'2007'!D2))),"")

control+shift +enter

copy across and down

<> different from 2007'!$G$5 (yes)

with condition '2007'!$G$2:$G$7='2007'!$G$5 evrything is blank . No match between hits -ab-run and '2007'!$B$2:$B$7&" week"&'2007'!$A$2:$A$7

sorry for my English