Double Condition (where sumproduct won't work)

[Guanjin Peter]

my table is something like below:
my program sheet looks something like this:
I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.
I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.

date: 14 Jun (dd / mm)
product -- Doc#
apple ---- ac21 (so what formula do I put here?)
Orange --- abc2 (formula?)


date --- product --- Doc#
12 Jun -- apple ----- abc1
12 Jun -- Orange --- ab12
14 JUn -- Apple ----- ab21
14 Jun -- Orange ---- abc2

 

[Guanjin Peter]

tried sum product and index match ....still can't get it....

 

[Guanjin Peter]

=SUMPRODUCT(--(db!A$2:A$10=B2),--(db!D$2:D$10=B9),db!K$2:K$10)
I put a new clumn K as index: 1,2,3,4,5,6,7
>>> This formula returns the values of added up values of K, so I can't effectively get the row number.

=INDEX(db!H2:H7,IF(MATCH(B11,db!D2:D7,0)=MATCH(B2,db!A2:A7,0),MATCH(B2,db!A2:A7,0),""))
>>> Was trying to makes 2 MATCH formula to match then return row number. Didn't work too.

If I can get the row number via 2 conditional matches(returns the 1st row number with simliar entries)....it should be really easy.

 

[Peter_SSs]

Hi Peter_SSS, Sorry for late reply. I just checked. Can assume Doc# will be the same it date and product is the same. So it means by table assumption is wrong. So it should be like this:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">date </TD><TD></TD><TD></TD><TD>product</TD><TD></TD><TD></TD><TD></TD><TD>Doc#</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">12-Jun</TD><TD></TD><TD></TD><TD>Apple</TD><TD></TD><TD></TD><TD></TD><TD>abc1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">12-Jun</TD><TD></TD><TD></TD><TD>Orange</TD><TD></TD><TD></TD><TD></TD><TD>ab12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">14-Jun</TD><TD></TD><TD></TD><TD>Apple</TD><TD></TD><TD></TD><TD></TD><TD>ab21</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">14-Jun</TD><TD></TD><TD></TD><TD>Orange</TD><TD></TD><TD></TD><TD></TD><TD>abc2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">14-Jun</TD><TD></TD><TD></TD><TD>Apple</TD><TD></TD><TD></TD><TD></TD><TD>ab21</TD></TR></TABLE>

But doc# can be different, if date is different but same product.
I'm really sorry Peter for the changes, I didn't expect this until now.

In that case use Aladin's formula:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	date			product				Doc#
2	12-Jun			Apple				abc1
3	12-Jun			Orange				ab12
4	14-Jun			Apple				ab21
5	14-Jun			Orange				abc2
6	14-Jun			Apple				ab21
7
db

Excel Workbook
	A	B	C	D	E
1		date		Doc#
2		14-Jun		ab21
3
4
5
6
7
8		product
9		Apple
10
Match multiple

 

[Guanjin Peter]

thank you so much! That solves it! :>
Should have tried that formula again...


Well, I'm finally one happy tiger now.

 

[Guanjin Peter]

Hmm...if it is invalid it'll show #N/A, the puzzling thing is, when I search Column C to replace #N/A with "", it says it found none! Am I missing something?

 

[Peter_SSs]

Hmm...if it is invalid it'll show #N/A, the puzzling thing is, when I search Column C to replace #N/A with "", it says it found none! Am I missing something?

What does it have to do with column C? I thought we were using columns A, D and H!

As pointed out earlier, #N/A will occur if the date and product combination does not occur on the "db" sheet.

That is, given my small sample sheets, you will get #N/A if ...
a) B2 or B9 on the formula sheet is blank, or
b) the date/product combination on the formula sheet does not exist on the "db" sheet. For my small sample, try putting "zzz" in B9 and you will see.

 

[Guanjin Peter]

hmm okay. Sorry was testing out random things, so I tried on column C. Can I return a "" value if not found? Or is it possible to replace the whole column of #N/A with ""?

 

[Peter_SSs]

... is it possible to replace the whole column of #N/A with ""?

What column of #N/A? You said previously

when I search Column C to replace #N/A with "", it says it found none!

Can I return a "" value if not found?

Sure, here's one way:

=IF(ISNA(LOOKUP(2,1/((db!A1:A13=B2)*(db!D1:D13=B9)),db!H1:H13)),"",LOOKUP(2,1/((db!A1:A13=B2)*(db!D1:D13=B9)),db!H1:H13))

 

[Aladin Akyurek]

What column of #N/A? You said previously


Sure, here's one way:

=IF(ISNA(LOOKUP(2,1/((db!A1:A13=B2)*(db!D1:D13=B9)),db!H1:H13)),"",LOOKUP(2,1/((db!A1:A13=B2)*(db!D1:D13=B9)),db!H1:H13))

Peter,

The following should be less expensive:

=LOOKUP(REPT("z",255),
   CHOOSE({1,2},
     "",
     LOOKUP(2,1/((db!A1:A13=B2)*(db!D1:D13=B9)),db!H1:H13)))

 

[Peter_SSs]

Peter,

The following should be less expensive:

=LOOKUP(REPT("z",255),
   CHOOSE({1,2},
     "",
     LOOKUP(2,1/((db!A1:A13=B2)*(db!D1:D13=B9)),db!H1:H13)))

Thanks Aladin - clever thinking - as usual.