Match Function giving wrong output results.

[deuce]

Hi, I am using the match function ="K"&MATCH(N20,K:K,0) to find the relative location of the cell with the maximum number stated in N20 however in the column it references (K:K) there are multiple cells with the same maximum number. what the formula does it get's the first cell containing the max number in the column with the highest count then gives wrong results...

What I need in the solution is for the formula to refer to the cell M21 which contains the mode referring then to column J to find the same mode as in M21 then give the relative position of the cell beside it in column K.

For example the mode is determined to be 5 (M21) with 79 (column K) counts but then all the numbers in J (column J) of which M21 is one of the values have the same amount of counts, the formula based on the value of M21 will refer to the cell in the J column looking at 5 then output the column/row from the cell beside it in column K. The correct result would be column/row K5.

J K
1 79
2 79
3 79
4 79
5 79
6 79

 

[DonkeyOte]

Sounds as though you might want to use a SMALL Array.

N21:
=IF(ROWS(N$21:N21)>COUNTIF($K:$K,$N$20),"","K"&SMALL(IF($K$1:$K$1000=$N$20,ROW($K$1:$K$1000)),$M21))
confirmed with CTRL + SHIFT + ENTER (copied down as nec.)

above assumes N20 holds MAX, M21 holds k for the SMALL.

Note: use of 1:1000 rather than K:K ... entire column references are not permitted with Arrays prior to XL2007 nor should they ever be used... keep as lean as possible (use a Dynamic Named Range as nec.)

Not sure why you want the literal cell reference (Kn) ... rarely needed.

 

[deuce]

hi your formula gives me the same output results as the match formula.

I need the relative cell location to be displayed correctly it's required for another formula to be calculated properly.

 

[DonkeyOte]

If the Array was entered correctly it would return a different result in each cell.... to confirm the array is set check that the formula is encased within { } ... if not try again following the steps outlined above.

 

[deuce]

hi, i require only the correct cell address from the K column i.e. cell beside the mode (M21 & J column).

can you try with the example I provided?

 

[deuce]

in other words the mode is in cell M21 look in column J for it's location then give the relative address of the column/row to it's right in this case column K/Row???.

there is only 1 correct result displayed like that rather than multiple outputs.

I entered the array formula correctly and got the same incorrect output as the match formula given in my initial post.


thanks and regards.
deuce.