Lookup Question

[andrewc]

Hi,

Apologies, this is related to my earlier post. I've created a new one as I couldn't see how to add a file when I edited the post. Hopefully this one is more clear too!

Sample source data is in columns A to C, and the lookup table is in columns G to I.

In the lookup table I’m trying to extract the value from column C that is associated with the product and country indicated in columns G and H.

The difficulty is how to deal with the blank in cell C6 as, in this example, a blank is not the same thing as a zero.

Can someone please suggest how I can amend the formulae in column I to accommodate the distinction between a blank cell and one containing the number zero.

Thanks very much!

[Special-K]

This will do the same thing

=VLOOKUP(G2,IF(B$2:B$7=H2,A$2:C$7),3)
Array formula, use Ctrl-Shift-Enter

But you have the same problem, no distinguishing between 0 and a blank.
Trouble is, as soon as the VLOOKUP determines it's a blank it gets converted to zero.
Can't presently think of a way round this.

[sweep]

Hi,

This array formula works for your example

Please Login or Register  to view this content.

Confirm with CTRL, SHIFT and ENTER

[GeneralDisarray]

This is a bit long, but it will do what you what:

=CHOOSE((SUMPRODUCT(($A$2:$A$7=$G3)*($B$2:$B$7=$H3)*($C$2:$C$7=""))>0)*1+1,SUMPRODUCT(($A$2:$A$7=$G3)*($B$2:$B$7=$H3)*$C$2:$C$7),"")

The part in red is there just to figure out if you have a 'blank' case - if so a blank is returned. Otherwise, your usual function (in blue) is calculated.

[andrewc]

Thanks very much!