CODEWITHSUNDEEP
excelmatchexcel-formulaoffsetmin
Jeff Brady
Jeff Brady

Reputation: 1498

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?

Upvotes: 0

Views: 3864

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 1

Related Questions