# Double Condition (where sumproduct won't work)

#### Guanjin Peter

##### Active Member
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Guanjin Peter

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

Code:
``=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

##### MrExcel MVP, Moderator
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

Regards
Jon

#### Guanjin Peter

##### Active Member
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

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

##### MrExcel MVP, Moderator
Maybe something like this?

Excel Workbook
ABCDEFG
1dateproductDoc#dateproductDoc#
212-JunAppleabc114-JunAppleab21
312-JunOrangeab1214-JunOrangeabc2
414-JunAppleab21
514-JunOrangeabc2
6
7
Match multiple

#### Guanjin Peter

##### Active Member
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)))

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFGH
1dateproductDoc#
212-JunAppleabc1
312-JunOrangeab12
414-JunAppleab21
514-JunOrangeabc2
6
db

Excel Workbook
ABCDE
1dateDoc#
214-Junab21
3
4
5
6
7
8product
9Apple
10
Match multiple

##### MrExcel MVP
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

##### Active Member
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>

Replies
4
Views
204
Replies
3
Views
1K
Replies
3
Views
1K
Replies
2
Views
225
Replies
15
Views
906

1,191,691
Messages
5,988,110
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back