Match multiple criteria in rows and columns

**JuJuBe** · 06-01-2010, 06:30 PM

I have a spreadsheet, (see attached), where I need to match multiple criteria in rows and columns to find a best possible solution.

I need a formula in D2 that says: if C2 = "yes", look for a matching item number in column A with a lower cost in column B. True ="yes", false="no"

Thanks in Advance!

**Paul** · 06-01-2010, 06:53 PM

Hi JuJuBe,

Perhaps the following array formula will work (confirmed with CTRL+SHIFT+ENTER):

=IF(AND(C2="yes",MIN(IF($A$2:$A$39=A2,$B$2:$B$39))<B2),"Yes","No")

**JuJuBe** · 06-01-2010, 07:08 PM

Thanks a million Paul!

**JuJuBe** · 06-02-2010, 12:35 PM

You guys are a huge help, I am learning so much.

Can we take this a bit further? See the updated example.

I'd like to write a formula in column H that says; If F="yes", find the matching item # with a lower unit cost, convert the May Purchase Qty (G), to the number of items to be purchased using the new item's unit size.

Make any sense? Sorry, I'm confused too.

JuJuBe

**day92** · 06-02-2010, 02:39 PM

This should work in column H.

=MIN(IF($F$2:$F$39="YES",$B$2:$B$39,0))

Im not sure what you need in column G.

**JuJuBe** · 06-02-2010, 03:47 PM

I'm not explaining what I'm looking for very well. See the updated example spreadsheet attached.

Let's say I am buying bolts. The different bolts come in cases of different qty's. Some items have a better per bolt price when purchased in different case sizes than I am buying now, others don't. We already found the items that have a better price, this is in column F. Now I want to find how many cases I have to purchase of the new, better priced item # to maintain the total qty of bolts purchased per item # during May.

Thank you