Return the lowest value in one column based on a text search in a different column.

**Juliana33** · 10-11-2012, 09:06 AM

Hi everyone,

I have a table with 4 columns. In the first one "A" I have a date, then the name of the product in "B", then the name of the company from which it is purchased "C" and than the price "D". The products are 341 (unique ones) in total but they repeat since each one has been purchased many times (there are 2000+ rows).

I have generated an alphabetical list with th 341 unique products. Next to each one I am trying to write a formula, which checks coloumn B, identifies the rows containing the first product in my alphabetical list and then returns the lowest value from column D. Next to it, in the next column I want to have the name of the company (from column C) which gives this lowest value.

Thank you very much in advance!

Regards,
Juliana

**Fotis1991** · 10-11-2012, 09:15 AM

Hello to you. Welcome to the forum.

You need an Array =MIN(IF(......)) for this.

Pls upload a sample workbook for this.

**zbor** · 10-11-2012, 09:15 AM

Use Pivot table

**mikerickson** · 10-11-2012, 09:16 AM

I'm not sure about your lay-out, but the CSE formula =MIN(IF(B1:B1000="apple", D1:D1000)) will be the lowest price paid for an apple.

=INDEX(C1:C1000, MATCH(MIN(IF(B1:B1000="apple", D1:D1000)), IF(B1:B1000="apple", D1:D1000), 0), 1) will be the company that sold that cheap apple.

These formulas should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

**twiggywales** · 10-11-2012, 09:18 AM

its easier to sort this if you add a work book but somthing like this

min array.xlsx

entered with CTRL+SHIFT+ENTER)

Please Login or Register  to view this content.

**Juliana33** · 10-11-2012, 09:40 AM

Thanks for the welcoming words and for the answers.

Here is a short version of my table. I did Use a pivot table. Unfortunately, I can only calculate the min value and I don't know how to return the name of the company which corresponds to it. This is why I am looking for a formula. Return the lowest price and the name of the company.xlsx

**twiggywales** · 10-11-2012, 09:49 AM

see my attched book it may help

min array (1).xlsx

**zbor** · 10-11-2012, 09:50 AM

Pull under product another row for companies

**Juliana33** · 10-11-2012, 09:58 AM

Thank you very much! The array formula did work out!

Return the lowest value in one column based on a text search in a different column.

LinkBack

Thread Tools

Rate This Thread

Display

Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Re: Return the lowest value in one column based on a text search in a different column.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions