CODEWITHSUNDEEP
excelexcel-formula
Jeff Brady
Jeff Brady

Reputation: 1498

How to get MIN of a range, along with a 2nd cell in same row?

Thank you to the Excel gurus of stackoverflow for helping me with what seems to be an impossible project. I have another question:

I have 2 worksheets, Sheet1 and Sheet2.

Sheet1 has a list of product codes in A and a formula to get the lowest price from Sheet2 in B:

A      B      C
A100   $10
A200   $12
A300   $45

Sheet2 has a list of product codes, prices, and descriptions:

A      B      C
A100   $20    Product One
A100   $10    Product Two
A100   $12    Product Three
A100   $0     Not Found
A200   $25    Product A
A200   $12    Product B
etc, etc, etc

In Sheet1, column B, I'm using the following formula to find the lowest non-zero price:

=MIN(IF('Sheet2'!$A$1:$A$20000=A1,IF('Sheet2'!$B$1:$B$20000>0,'Sheet2'!$B$1:$B$20000)))

How can this be modified to also grab the description in column C and place it in Sheet1 column C?

As always, thank you for your time and effort!

Upvotes: 1

Views: 271

Answers (3)

barry houdini
barry houdini

Reputation: 46331

You can use an INDEX/MATCH solution, e.g. for C1

=INDEX(Sheet2!C$1:C$20000,MATCH(1,(Sheet2!A$1:A$20000=A1)*(Sheet2!B$1:B$20000=B1),0))

confirm with CTRL+SHIFT+ENTER

Upvotes: 1

JAGAnalyst
JAGAnalyst

Reputation: 330

Because both your Product Code and Price columns within Sheet2 will likely have duplicate values, I'd recommend creating a unique value in Sheet2 that you can use to differentiate between different price/product code combinations. Here's how I addressed this:

Step 1 - Insert a New Column in Sheet2

I added a new column before the current column A with the following formula:

=CONCATENATE(B2,"-",C2)

This revision will now yield the following values for Sheet2:

A          B          C          D
A100-20    A100       20         Product One   
A100-10    A100       10         Product Two
A100-12    A100       12         Product Three
A100-0     A100       0          Not Found

Step 2 - Enter a new Formula in column C in Sheet2

=VLOOKUP(CONCATENATE(A2,"-",B2),Sheet2!A:D,4,FALSE)

This will look up the combination of values in columns A and B from Sheet1 from the data in Sheet2. Additional error-checking could be added to ignore zero-priced products, but I have not included this in my formula as you already accounted for this in your previous formula in Sheet1 column B. When I tested it, this produced the correct description.

If you can't modify the worksheet to this extent, you could also use an iterative VBA-based solution, which I'd be happy to demonstrate if necessary.

Good luck!

Upvotes: 0

Peter L.
Peter L.

Reputation: 7304

I think I got the answer) Put the following in C1:

=IFERROR(INDIRECT("Sheet2!C"&MATCH(A1&MIN(IF(Sheet2!$A$1:$A$20000=A1,IF(Sheet2!$B$1:$B$20000>0,Sheet2!$B$1:$B$20000))),Sheet2!$A$1:$A$20000&Sheet2!$B$1:$B$20000,0)),"No price / description found")

and don't forget to press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!). Error handling is included as well.

In case 1-cell solution with combined price AND description required - use this monster:

=IFERROR("$"&MIN(IF(Sheet2!$A$1:$A$20000=A1,IF(Sheet2!$B$1:$B$20000>0,Sheet2!$B$1:$B$20000)))&": "&INDIRECT("Sheet2!C"&MATCH(A1&MIN(IF(Sheet2!$A$1:$A$20000=A1,IF(Sheet2!$B$1:$B$20000>0,Sheet2!$B$1:$B$20000))),Sheet2!$A$1:$A$20000&Sheet2!$B$1:$B$20000,0)),"No price / description found")

Sample file is available as well: https://www.dropbox.com/s/tnb3ov7o1s2sper/PriceWithDescr.xlsx

Upvotes: 1

Related Questions