# VLOOKUP function finding MIN value

#### gateway123

##### New Member
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

### 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
Try
=MIN(IF(A1:A11=C1,B1:B11,9.99E307))

Use Ctrl+Shift+Enter to enter the formula.

#### gateway123

##### New Member
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
____________________________________________________________
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
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
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

Replies
7
Views
82
Replies
7
Views
59
Replies
11
Views
138
Replies
3
Views
64
Replies
3
Views
38