VLOOKUP function finding MIN value

gateway123

New Member
Joined
Jul 18, 2012
Messages
5
Hi, I got 2 columns I want to calculate the minimum value corresponding each number 1 and 2. I don't know how to put the min function in it.

=VLOOKUP(C1:C2,A1:B11,2,False)

1 0.01 1
1 0.00 2
1 0.04
1 0.09
1 0.04
1 0.06
2 2.01
2 3.00
2 7.04
2 1.09
2 2.04
2 0.06


I am hoping results should look like this
1 0.00
2 0.06
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
Try
=MIN(IF(A1:A11=C1,B1:B11,9.99E307))

Use Ctrl+Shift+Enter to enter the formula.
 

gateway123

New Member
Joined
Jul 18, 2012
Messages
5
Thankyou, it works. I got another question what if there is another column for these values and I am also interested in fetching that value e.g

1 2000 0.01
1 2001 0.00
1 2002 0.04
1 2003 0.09
1 2004 0.04
1 2005 0.06
2 2000 2.01
2 2001 3.00
2 2002 7.04
2 2003 1.09
2 2004 2.04
2 2005 0.06

Now still I want to get least value for 1 and 2 but I also want the year associated with that values.
One way would be after finding leas values using a Vlookup function on these least values to get the year but in that case there are many values who are occuring multiple times in the data. So what else would be a solution.

Results should look like

1 2001 0.00
2 2005 0.06
 

gateway123

New Member
Joined
Jul 18, 2012
Messages
5
____________________________________________________________
Say that formula is in E1.
F2: =index(b$1:b$12,match(E1,c$1:c$12,0))
Note, if the max appears more than once this will result in an error.
____________________________________________________________



I have multiple entries of these values so it did not work.
 

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
if you have multiple entries at the max, what would you like it to return... the earliest year or the latest year?
 

gateway123

New Member
Joined
Jul 18, 2012
Messages
5
1 2000 0.01
1 2001 0.00
1 2002 0.04
1 2003 0.09
1 2004 0.04
1 2005 0.06
2 2000 2.01
2 2001 3.00
2 2002 7.04
2 2003 1.09
2 2004 2.04
2 2005 0.06

e.g in this data I need the least value that is 0.00 for 1 and 0.06 for 2. I need the year it was measured.

So the result is on the basis of the least value 0.00 and 0.06 and then i need the corresponding years these values were taken.

I am hoping to get result like this

1 2001 0.00
2 2005 0.06

Leas values and their corresponding years.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,108,841
Messages
5,525,166
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top