VBA/formula: if there's a cell in column B = 1, column D = 1, select that row and copy value in column H

[JennV]

Hello there,

I'm hoping to get some help/insight on how I'm able to complete this task.

On Sheet 1:
[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"] H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.3[/TD]
[/TR]
</tbody>[/TABLE]

On Sheet 2, for example, I want the value in column H in the row where column B=1 and column D=1 (in this case, "1.1").
Another example, I want the value in column H in the row where column B=1 and column D=2 (in this case, "4.3").

Thank you in advance!

 

[JennV]

Try something like this

=IF(<formulahere>formulahere=0,"",formulahere<formulahere>)

M.</formulahere></formulahere>

Sheet1:
[TABLE="width: 448"]
<colgroup><col width="64" style="width:48pt" span="7"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]1.1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2:
[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]1.1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you for that formula (works great for [1,2] - blank on Sheet1)! In the event that there is data equal to zero ([2,3]), the formula would make it blank on Sheet2 but I don't want it to be blank; it should display zero if Sheet1 says zero. Any workaround for this? Again, thank you VERY much!

 

[Marcelo Branco]

Try

Sheet2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1,1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2,2​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

3​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in C1 copied down
=IF(ISNUMBER(INDEX(Sheet1!H$1:H$5,MATCH(1,INDEX((Sheet1!B$1:B$5=A1)*(Sheet1!D$1:D$5=B1),),0))),SUMIFS(Sheet1!H$1:H$5,Sheet1!B$1:B$5,A1,Sheet1!D$1:D$5,B1),"")

M.

 

[JennV]

Try

Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1,1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2,2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C1 copied down
=IF(ISNUMBER(INDEX(Sheet1!H$1:H$5,MATCH(1,INDEX((Sheet1!B$1:B$5=A1)*(Sheet1!D$1:D$5=B1),),0))),SUMIFS(Sheet1!H$1:H$5,Sheet1!B$1:B$5,A1,Sheet1!D$1:D$5,B1),"")

M.

Amazing! Thank you so, so much!

I was playing around with it last night as well and discovered an alternative option, in case someone else is in the same boat as me:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR(IF(ISBLANK(INDEX(Sheet1!$1:$1048576,MATCH(1,(Sheet1!$B:$B=Sheet2!B14)*(Sheet1!$D:$D=Sheet2!C14),0),8)),"",INDEX(Sheet1!$1:$1048576,MATCH(1,(Sheet1!$B:$B=Sheet2!B14)*(Sheet1!$D:$D=Sheet2!C14),0),8)),"-")

*need to CTRL+SHIFT+ENTER

This displays blanks as blanks and zeros as zeros. In addition, if your criteria are not met, it will display "-". I added this because I have a use for it.

Thank you again, M.[/FONT]