Min value in Vlookup

[senthilkumar_rm]

Hi Solvers,


Suppose i have data like this

[pre]

A        B
Type	Value

Part 1	3.62
Part 1	2.63
Part 1	3.65
Part 1	2.00

[/pre]
When i tried to put a vlookup for part 1 it retrieves only the first value whereas i want the min value

answer retrieved is 3.62 but i need 2.00 (Min value of same part #)

 

[Debraj]

Hi Senthil,


Try this...

=MIN(IF($A$1:$A$5="Part 1",$B$1:$B$5))


or


=SMALL(IF($M$3:$M$6="Part 1",$N$3:$N$6),1)

Regards,

Deb

 

[senthilkumar_rm]

Thanks Roy


But i have data of whole list , I need it on Vlookup

 

[vijay.vizzu]

Dear SenthilKumar_rm


Why don't you use PIVOT Table for this, Its very simple


Insert Pivot Table and put Value in values label and click value field settings and select min instead of sum.


Regards

 

[Debraj]

Hi Senthil,


What actually you need!!

* A formula where.. VLOOKUP word should be there.. and data with whole list..

* You don't have any problem with Length of Formula & processing time..


then try below

=VLOOKUP("Part 1",OFFSET(A1,MATCH(MIN(IF(A:A="Part 1",B:B)),B:B,0)-1,0,,2),2,0)

Regards,

Deb

 

[Debraj]

Hi Senthil,


Sorry! I forgot to mention.. above all formula are Array Formula..

You need to confirm the formula with Ctrl + Shift + Enter , not just Enter..


Regards,

Deb