Finding the locations of the number in range

[bines53]

Hello friends !

I have a formula, but I am looking for another ,without use array.

Thank you !

 

[Luke M]

It would be much easier if you could explain what the formula is attempting to calculate. Otherwise, I might as well propose a formula of
=CHOOSE(ROW(A1),2,3,5)
to replace the 3 you have now.

 

[bines53]

Hi Luke ,

Column M the my formulas , I want to find the number of rows that appear 3.

The number 3 appears in three different lines, lines 2 3 5.

The formula you gave, is not accurate, because if I write the number 3, in line 10, for example ,Your formula, do not give an answer 10.

Thank you !

David

 

[NARAYANK991]

Hi David ,

I am not clear on what you mean by :

without use array.

But the following array formula , to be entered using CTRL SHIFT ENTER , should give the same results :

=LARGE(IF($A$1:$F$11=$J$1,ROW($A$1:$F$11)),3)

=LARGE(IF($A$1:$F$11=$J$1,ROW($A$1:$F$11)),2)

=LARGE(IF($A$1:$F$11=$J$1,ROW($A$1:$F$11)),1)

This will work only if your data starts from row 1 ; if it starts from any other row , then the above formulae will need to be modified slightly.

Narayan

 

[Luke M]

If you really need a non-array formula, can also do this in M1
=IFERROR((1/SUMPRODUCT(LARGE(($A$1:$F$11=$J$1)*(ROW($A$1:$F$11)),ROWS(M1:M$3))))^-1,"")

Copy down to row 3. Will return top 3 rows. If fewer than 3 rows have desired value, then cell displays "".

 

[Somendra Misra]

Hi David,

If you Excel 2010+ than below function is a non-array solution which uses a little clean up version of your formula:

=AGGREGATE(14,6,MMULT(COUNTIF($J$1,$A$1:$F$11),{1;1;1;1;1;1})*ROW($A$1:$F$11),ROWS(M1:M$3))

Regards,

 

[bines53]

Thank you all for your help !

Luke , Somendra

Inaccurate formulas, put them in a file, I added the number to 3 more rows .

David

 

[Somendra Misra]

@bines53

See the file with corrected formula, now you have five 3's, so you need to adjust ROWS(M1:M$3) to ROWS(M1:M$5).

Regards,

 

[bines53]

Hi Somendra,

Awesome!!!

I love the combination of the two functions AGGREGATE +MMULT !
Somendra,Tell me if I'm right, that the use of this function AGGREGATE , reduces it most the need to array {} ?

Thank you !

 

[Somendra Misra]

@bines53

AGGREGATE first argument with numbers greater than equal to 14, can handle arrays, just like SUMPRODUCT function. It does not require any special key stroke for this function.

Regards,

 

[Luke M]

Nice formula SM.

Bines53, do note AGGREGATE is an Office 2010+ formula. Won't work well if you have users in 2007 or earlier.