CODEWITHSUNDEEP
excelexcel-formula
k111
k111

Reputation: 103

Matching comparing values in a row from four columns in Excel

I want to write a function that can compare the values from a row in all the columns. And it prints a defined character for the no. of matches. Like if the 2 matches prints 'p', if 3 then 'q' and for 4 is 'r'. So what will be the function?

The representation is a bit like that:

The representation is a bit like that.

For the column A1 to column D1, it matches 2 characters so the value is 'p'. Subsequently, the pattern follows of matches according to this.

Upvotes: 1

Views: 33

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

I would use FREQUENCY as follows:

=CHOOSE(MAX(FREQUENCY(MATCH(A1:D1,A1:D1,0),MATCH(A1:D1,A1:D1,0))),"o","p","q","r")

entered as an array formula using CtrlShiftEnter

If you were generalising it to more than 4 columns, this might be better:

=CHAR(MAX(FREQUENCY(MATCH(A1:D1,A1:D1,0),MATCH(A1:D1,A1:D1,0)))+CODE("n"))

Upvotes: 2

Related Questions