Lookup formula

[Rodrigues]

Hi All
I would like a formula to do the following:

Look on all values from entire column B and display on cell D1 onwards (E1, E3,etc) by the smallest number.
Could someone help me with this, please?
Thanks R

 

[John Jairo V]

Hi @Rodrigues

Put this formula into D1 and drag it to the right:

=SMALL($B$1:$B$9,COLUMNS($D1:D1))

Blessings!

 

[Rodrigues]

Thanks for your prompt help.

 

[bines53]

Hi Rodrigues,

Another one,
=AGGREGATE(15,6,$B$1:$B$9,COLUMN(A1))

David

 

[Rodrigues]

Excellent thanks.
Sorry to be a pain forgot to ask that, how to avoid duplicate values.
Normally I use "<>" or "<>0" but can't make it work.
Help please?
Thanks
R

 

[Asheesh]

Hi,

Use the below

AGGREGATE(15,6,IF(FREQUENCY($B$1:$B$9,$B$1:$B$9)>=1,$B$1:$B$9),COLUMN(A1))

To be acknowledged with Ctrl + Shift + Enter

See the attached for ref

 

[John Jairo V]

Hi to all!

Another option could be (in D1; No CSE):
=SMALL($B$1:$B$9,1+SUMPRODUCT(COUNTIF($B$1:$B$9,$C1:C1)))

And drag it right.
Blessings!

 

[Asheesh]

@John Jairo V - Beautiful...

 

[Rodrigues]

John,
So I can just understand better the formula what C1:C1 refers to? All the rest I got it.
Thanks again.
R

 

[bines53]

Hello friends,

The solution is not sexy, like a master,John.

=AGGREGATE(15,6,$B$1:$B$9/(MATCH($B$1:$B$9,$B$1:$B$9,0)=ROW($B$1:$B$9)),COLUMN(A1))

Hi Asheesh,

Your solution is excellent, it works even without CSE.

David

 

[XOR LX]

Yes, such is the array-capability of FREQUENCY that not only do you not require CSE, you don't even require AGGREGATE.

=SMALL(IF(FREQUENCY($B$1:$B$9,$B$1:$B$9),$B$1:$B$9),COLUMNS($A:A))

without CSE, will work just fine.

Regards

 

[bines53]

Hello friends,

Another one,
=AGGREGATE(15,6,$B$1:$B$9/($B$1:$B$9>D2),1)

Cell D2 should be empty, and drag to the right.

David