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]

*hadouken*
please lemme know if this needs more clarity, basically if the returned value is only number then the sumProduct solution is:

=SUMPRODUCT(--(sheet1!$A$2:$A4=$a$1),--(sheet1!$B$2:$B5=B$2),$C$1:$4)

assuming A1 is date
A2 is product

sheet1 is the table above,
where A date column
B is product Column
C is Doc# column

 

[Jon von der Heyden]

Hi

Using the table below:

=INDEX($C$1:$C$13,MATCH(F3,IF(A1:A13=F2,B1:B13),0))

confirmed with Control + Shift + Enter, not just enter because this is an array formula.

F2 houses criteria 1 (e.g b)
F3 houses criteria 2 (e.g. x)

A B C
1 a x h
2 b y i
3 c x j
4 a y k
5 b x test
6 c y m
7 a x n
8 b y o
9 c x p
10 a y q
11 b x r
12 c y s
13 a x t

Analysis
[Table-It] version 09 by Erik Van Geit

Adjust to meet your needs.

Regards
Jon

 

[Guanjin Peter]

thanks for the reply. Is it possible to do it without alt+shift+enter?

Like once I just vba code to add the formula to the cell, it'll directly return the value?
or is there another way around?

 

[Stormseed]

Is it possible to do it without alt+shift+enter?

There is no ALT here. It is Ctrl+Shift+Enter.

@Jon:

I lliked your earlier Avatar (personal picture) more than the current one. I changed mine to a cartoon though

 

[Peter_SSs]

Maybe something like this?

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

 

[Guanjin Peter]

Thanks so much! it was exactly what I'm looking for!

When I modify it to reference at another sheet, it doesn't work anymore
it says there's an error @ index db!H:H error returned: #num!
but if I use H:H it works! ...wha's wrong?

=INDEX(db!H:H,SUMPRODUCT(--(db!A$2:A$100=B2),--(db!D$2:D$100=B9),ROW(db!A$2:A$100)))

 

[Peter_SSs]

We haven't seen your "db" sheet or the sheet this formula is on, but from looking at your modified formula, it seems to me that ...

On the db sheet:
- the dates are in column A.
- the product is in column D.
- the Doc# is in column H

On the formula sheet:
- the date of interest is in B2.
- the product of interest is in B9.

Given all that, the formula works for me. So what's different with your setup?

Here are my two sheets:

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
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

 

[Aladin Akyurek]

thanks for the reply. Is it possible to do it without alt+shift+enter?

Like once I just vba code to add the formula to the cell, it'll directly return the value?
or is there another way around?

If you can't work with Jon's suggestion:

=INDEX($C$1:$C$13,MATCH(F3,IF(A1:A13=F2,B1:B13),0))

try the following...

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

 

[Guanjin Peter]

oh...I understand now...because I got duplicate entries for product and date:
That causes the formula not to work, even with different doc#...
<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></TBODY></TABLE>
<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="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>14-Jun</TD><TD></TD><TD></TD><TD>Apple</TD><TD></TD><TD></TD><TD></TD><TD>def</TD></TR></TBODY></TABLE>
<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="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>14-Jun</TD><TD></TD><TD></TD><TD>Orange</TD><TD></TD><TD></TD><TD></TD><TD>xyz</TD></TR></TBODY></TABLE>