Search code examples
excelmatchexcel-formulaoffsetmin

Can I use Offset and Match to find the MIN value in a range of results?

I have a workbook with 2 worksheets, Sheet1 and Sheet2.

Sheet1 has a value (product code) to look up, and an empty cell to fill:

A      B
A100
A200
B150
C3AB

Sheet2 has a bunch of product codes and costs:

A      B
A100   35.00
A100   14.00
A100   50.00
A200   10.00
A200   12.00
etc, etc, etc

I'm using the following to find a match between worksheets and populate column B in Sheet1:

=IFERROR(OFFSET(Sheet2!$B$1,MATCH(A1,Sheet2!$A:$A,0)-1,0), "")

Can I throw a MIN in there somewhere to pull out the lowest value in column B of Sheet2?

Solution

  • Try this

    Put this array formula in B1 of Sheet1 and press CTL + SHIFT + ENTER

    =MIN(IF(Sheet2!$A$1:$A$5=A1,Sheet2!$B$1:$B$5))

    I am assuming that your data range in Sheet2 is from A1:B5. Change as applicable.

    ScreenShot

    enter image description here