Ignoring Blank Cells in IF statement

Here's my problem.

I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example:

Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price
Gloves | $4.59 | $5.00 |$6.00 | $3.56
Hats | $5.00 | | | $4.59

In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price.

My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is:

=IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP3","MYCOMPANY")))

This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes.

So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?

It sounds to me as though you want:

=INDEX($B$1:$E$1,MATCH(MIN($B2:$E2),$B2:$E2,0))
copied down

If you want MYCOMPANY instead of OurPrice then either change B1 to "MYCOMPANY" or encase the above within a Substitute, eg:

=SUBSTITUTE(INDEX($B$1:$E$1,MATCH(MIN($B2:$E2),$B2:$E2,0)),$B$1,"MYCOMPANY")

I would probably opt to change B1 myself...

i think donkyotes first one is the way to go with changing B1 to "my company"
the others are over engineered!
i was going to suggest similar
=INDEX($B$1:E2,1,MATCH(MIN($B2:$E2),$B2:$E2,0)) but there is no need to change the first bit $B$1:E2

Thanks Martin,

Yes I would say if you wanted to return the lowest price a standard MIN($B2:$E2) would be sufficient (the blanks will not be coerced to 0) ... and when using INDEX with a Vector as opposed to a Matrix there's no need to specify both row & column - either/or as appropriate will suffice.

only way is for us to see workbook concerned

Can MIN be modified to include column D in addition to P:R?

MIN can accept non-contiguous ranges if that's what you mean, eg:

=MIN(D2,P2:R2)

but of course the MATCH function can not, meaning the above approach can not be used in conjunction with the INDEX/MATCH to retrieve the header (as the MATCH can not accept a non-contiguous range).

The most obvious solution so as to be able to retrieve the header whilst simultanesouly avoiding inefficient formulae would be to add D contents to S (if viable) that it to say create one contiguous range... if this is not possible let us know.

One approach for handling non-contiguous range might be:

Please Login or Register  to view this content.