CODEWITHSUNDEEP
vbaexcellookup
Gideon
Gideon

Reputation: 313

Lookup most used text in range based on criteria

I have the following CSE formula to return the most used text in a range,excluding empty cells. 

=INDEX(A4:D4,MODE(IF(A4:D4<>"",MATCH(A4:D4,A4:D4,0))))

My problem is that the formula returns #NA when there is only one value in the range. How can I adjust the formula to return that value?

Upvotes: 0

Views: 59

Answers (1)

QHarr
QHarr

Reputation: 84465

If only concerned with a single cell being present causing problems and wanting to retrieve use this CSE:

=IF(COUNTIF(A4:D4,"*"),  INDEX(A4:D4,MATCH(FALSE,ISBLANK(A4:D4),0)),INDEX(A4:D4,MODE(IF(A4:D4<>"",MATCH(A4:D4,A4:D4,0)))))

Otherise, with all distinct values being present or no mode in general,

You can count the distinct values and use that tested against the number of columns. If equal there is no mode and so use If statement to default into handling the True.

=IF(SUMPRODUCT(1/COUNTIF(A4:D4,A4:D4))=COLUMNS(A4:D4),"Do Something",INDEX(A4:D4,MODE(IF(A4:D4<>"",MATCH(A4:D4,A4:D4,0)))))

Again, a CSE so enter with Ctrl + Shift + Enter.

This bit of above formula counts the unique values:

SUMPRODUCT(1/COUNTIF(A4:D4,A4:D4))

Upvotes: 2

Related Questions