Formula with Lookup formula to return a text value

[EvenS8245]

Hello all.

I am attempting to key a formula to pull back a most used text value for an induvial in Excel 2013.

All my searches have directed me towards an =INDEX(range, MODE(MATCH(range, range, 0 ))) formula. There are multiple criteria that I have to work with, such as an ID # and Date, which, I believe, will need an IF(AND formula.

So I need the formula to search for each person and return what was the most used issue for a particular date.

I have attempted using the formula below and I keep getting #VALUE!.

=INDEX(range1,MODE(IF(AND(range2=id,range3=rundate),MATCH(range1,range1,0))))



Actual formula in example:

=INDEX($D$2:$D$26,MODE(IF(AND($A$2:$A$26=G4,$C$2:$C$26=$H$3),MATCH($D$2:$D$26,$D$2:$D$26,0))))



I am far from an expert and the actual file that i'm working on has 23k rows of data, so I was hoping that someone could help point me in the right direction. Hopefully i'm not too far off.

 

[Domenic]

Try the following formula . . .

H4, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($D$2:$D$26,MODE(IF($A$2:$A$26=$G4,IF($C$2:$C$26=H$3,MATCH($D$2:$D$26,$D$2:$D$26,0)))))

Hope this helps!

 

[EvenS8245]

Try the following formula . . .

H4, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($D$2:$D$26,MODE(IF($A$2:$A$26=$G4,IF($C$2:$C$26=H$3,MATCH($D$2:$D$26,$D$2:$D$26,0)))))

Hope this helps!

Thank you! That worked.

 

[EvenS8245]

Try the following formula . . .

H4, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($D$2:$D$26,MODE(IF($A$2:$A$26=$G4,IF($C$2:$C$26=H$3,MATCH($D$2:$D$26,$D$2:$D$26,0)))))

Hope this helps!

If i needed to add an IFNA( to that formula. Would that go before the =INDEX? I would like the field to stay blank if NA.

So basically, it would read:

=IFNA(INDEX($D$2:$D$26,MODE(IF($A$2:$A$26=$G4,IF($C$2:$C$26=H$3,MATCH($D$2:$D$26,$D$2:$D$26,0))))),"") CTRL+SHIFT+ENTER

 

[Domenic]

Yes, that's correct . . .