Find minimum value in cells based on another cell

[Billy Hill]

I want to find the smallest number in column B on sheet 2 for each number on sheet 1 and put it in column H on sheet 1.


Sheet 1 has unique whole numbers starting in C20 and below as far as C63.


Sheet1:
C20 1
C21 2
C22 3


etc


Sheet 2 has the same numbers starting in A2 (A3, A4 etc) but each number may or may not duplicate 1 or more times. The adjacent cell (B2, B3, B4 etc) also has a decimal number that may or may not duplicate.


Sheet2:
A B
1 -1.0
1 -0.5
2 0.01
3 -0.1
3 -0.5
3 -0.3


I want:
H20 -1.0
H21 0.01
H22 -0.5



I tried using a vlookup() but am not sure how to (or if I can) get the vlookup() function to work inside a min() function.


I found the below function someone else made but I'm not getting it to work. I don't understand why he's got the * in there.


=MIN(IF((Sheet2!$A$2:$A$150=Sheet1!$C20)*(Sheet2!$B$2:$B$150),(Sheet2!$B$2:$B$150)))


TYIA.

 

[jtakw]

Hi,

Based on your description this should work for you.

In Sheet1, H20, enter Array formula, Must be confirmed by CSE (Control, Shift, Enter):

=MIN(IF(Sheet2!A$1:A$63=C20,Sheet2!B$1:B$63))

Then copied down.

 

[Billy Hill]

I'm at home now but set up Excel the same as what I have at work and using CSE made your code work. Will try it Monday. Thank you.

 

[jtakw]

You're welcome.