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

 

[Peter_SSs]

oh...I understand now...because I got duplicate entries for product and date:
That causes the formula not to work, even with different doc#...

That's right.

So what result would you be wanting if you were looking at 14-Jun and Apple?

 

[Guanjin Peter]

return the the doc#, but i wasn't prepared for same date and product and different doc#. I got a shock too

 

[Guanjin Peter]

=LOOKUP(2,1/((A1:A13=F2)*(B1:B13=F3)),C1:C13)

aladin: that didn't work for me, returns a N/A#

 

[Peter_SSs]

return the the doc#,

Which Doc#? First, second, last, all of them?

 

[Guanjin Peter]

in Column H(db sheet), it stores all the doc#, it may be the same and it may be different. This gonna be a headache for me....

thanks alot for your reply!
Below is the full db table (sorry if this is what you want to see, I thought it wasn't required)

user1.xls
	A	B	C	D	E	F	G	H	I	J
1	DeliveryDate(dd/mm/yy)	Months	Customer	Outlet	CustomerID	ProductID	Product	ExternalDoc#	Qty	Uom
2	01-07-08	June	OrderTemplate	BURGERKING--BEDOK	C0127-09	VFG-20247	WHOLETOMATO-BK	abc1	3	KG
3	01-07-08	June	OrderTemplate	BURGERKING--BEDOK	C0127-09	VFG-20127	ONIONRING	cba2	4	PKT
4	01-07-08	June	OrderTemplate	BURGERKING--AMARA	C0127-08	VFG-20127	ONIONRING	abc123	4	PKT
5	01-07-08	June	OrderTemplate	BURGERKING--AMARA	C0127-08	VFG-20188	SHREDDEDLETTUCE(BK)	def456	5	PKT
6	30-06-08	June	OrderTemplate	BURGERKING--AMARA	C0127-08	VFG-20247	WHOLETOMATO-BK	bbb	5	KG
7	31-06-2008	June	OrderTemplate	BURGERKING--AMARA	C0127-08	VFG-20127	ONIONRING	aaa	5	PKT
db

 

[Aladin Akyurek]

=LOOKUP(2,1/((A1:A13=F2)*(B1:B13=F3)),C1:C13)

aladin: that didn't work for me, returns a N/A#

This is set up for the exhibit Jon posted. So, you need to adjust it to your layout. Btw, it will return #N/A if at least one lookup value (F2 or F3) is absent...

 

[Guanjin Peter]

I did adjust it to my layout. Seems strange to me it still gives #N/A. Maybe i'll try again.

 

[Guanjin Peter]

I was thinking of a workaround. I came up with this:

if date(in db sheet, column A) match b2 AND product(db sheet, Column D) match b7,
return row number
then index, return column H of that row.
Can anyone help me with the formula?

 

[Peter_SSs]

I was thinking of a workaround. I came up with this:

if date(in db sheet, column A) match b2 AND product(db sheet, Column D) match b7,
return row number
then index, return column H of that row.
Can anyone help me with the formula?

To me you still haven't made it clear what result you want if multiple rows match the date and product.

For example, if the date of interest is 14-Jun and product of interest is Apple, then for the sheet below, which of these 4 results do you want on your other sheet?

a) ab21
b) xyz
c) both ab21 and xyz
d) nothing?

Excel Workbook
	A	B	C	D	E	F	G	H
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				xyz
7
db

 

[Guanjin Peter]

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><TBODY><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></TBODY></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.