MATCH function.

[ashliiii]

Suppose I have in cells A1 to A5, values, 8, 6, 7, 6, 5.

In cell B1, I have the following equation:

=match(A1, A$1:A$5, 0)

which I copy and paste in cells B2 to B5.

Then, the values in cells B1 to B5, are 1, 2, 3, 2, 5.

My question is, since 6 is repeated in cells A2 and A4, how should the formula in col B be modified so that I get values 1, 2, 3, 4, 5 in cells B1 to B5.

Thankyou

 

[mancemonster]

So is it just the row number you are looking for?

If so...

Highlight B1:5

& in B1 type

=ROW(1:5)

Confirmed with Crtl+Shift+Enter

Regards

Roger

 

[Gerald Higgins]

How does Excel know that the 6 in A2 is different from the 6 in A4 ?

All you're asking is for it to find 6, and that's exactly what it's doing.

 

[ashliiii]

The example I had is a simple Excel sheet to show what I wanted to achieve.

How can I modify the match function so that it can distinguish between repeated values such as the value 6.

I have a worksheet that I record the miles walked on the treadmill each day for the past 8 years or so. I wrote a "large" function to get the amounts walked, sorted highest to lowest and a "match" function to find the corresponding row number. As is, when there are repeated vlaues for the miles walkes, the match function cannot distinguish between the repeated values. This is my question?

Appreciate the help.

 

[Aladin Akyurek]

The example I had is a simple Excel sheet to show what I wanted to achieve.

How can I modify the match function so that it can distinguish between repeated values such as the value 6.

I have a worksheet that I record the miles walked on the treadmill each day for the past 8 years or so. I wrote a "large" function to get the amounts walked, sorted highest to lowest and a "match" function to find the corresponding row number. As is, when there are repeated vlaues for the miles walkes, the match function cannot distinguish between the repeated values. This is my question?

Appreciate the help.

Let A2:A10 house the treadmill values.

B2 enter and copy down:

=IF(ROWS($B$2:B2)<=ROWS($A$2:$A$10),LARGE($A$2:$A$10,ROWS($B$2:B2)),"")

C2, control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER($B2),SMALL(IF($A$2:$A$10=$B2,ROW($A$2:$A$10)),COUNTIF($B$2:B2,B2)),"")

The latter will yield the native row numbers that correspond to the values,
sorted with the LARGE function.