formula for combining

[Yossi]

thanks for taking your time and trying to help out


i would like book1 to be the results from book2 plus book3 in a formula

[TABLE="class: cms_table_cms_table, width: 100, align: left"]
<tbody>[TR]
[TD]book1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD]e[/TD]
[/TR]
[TR]
[TD]f[/TD]
[/TR]
[TR]
[TD]g[/TD]
[/TR]
[TR]
[TD]l[/TD]
[/TR]
[TR]
[TD]t[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table_cms_table, width: 100"]
<tbody>[TR]
[TD]book2[/TD]
[TD]book3[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]f[/TD]
[/TR]
</tbody>[/TABLE]

 

[BenMiller]

With book 2 and book 3 in columns A and B (let's say from A1:B8), put this array formula in C2 (you must leave C1 blank above it) and confirm it with CTRL+SHIFT+ENTER:

=IF(ISNA(INDEX($A$1:$A$8,MATCH(0,COUNTIF(C$1:C1,$A$1:$A$8),0))),INDEX($B$1:$B$8, MATCH(0,COUNTIF(C$1:C1,$B$1:$B$8),0)),INDEX($A$1:$A$8, MATCH(0,COUNTIF(C$1:C1, $A$1:$A$8),0)))

 

[T. Valko]

thanks for taking your time and trying to help out


i would like book1 to be the results from book2 plus book3 in a formula

[TABLE="class: cms_table_cms_table, width: 100, align: left"]
<TBODY>[TR]
[TD]book1
[/TD]
[/TR]
[TR]
[TD]b
[/TD]
[/TR]
[TR]
[TD]c
[/TD]
[/TR]
[TR]
[TD]d
[/TD]
[/TR]
[TR]
[TD]e
[/TD]
[/TR]
[TR]
[TD]f
[/TD]
[/TR]
[TR]
[TD]g
[/TD]
[/TR]
[TR]
[TD]l
[/TD]
[/TR]
[TR]
[TD]t
[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="class: cms_table_cms_table, width: 100"]
<TBODY>[TR]
[TD]book2
[/TD]
[TD]book3
[/TD]
[/TR]
[TR]
[TD]b
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]c
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]g
[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]l
[/TD]
[TD]e
[/TD]
[/TR]
[TR]
[TD]t
[/TD]
[TD]f
[/TD]
[/TR]
</TBODY>[/TABLE]

What version of Excel are you using?

 

[Yossi]

i tried this
why doesn't it work?
=IF(ISNA(INDEX(last_week_pivot_inven!$A$5:$A$99999,MATCH(0,COUNTIF('sub_total'!A$2:A2,last_week_pivot_inven!$A$5:$A$99999),0))),INDEX(current_week_pivot_inven!$A$5:$A$99999, MATCH(0,COUNTIF('sub_total'!A$2:A2,current_week_pivot_inven!$A$5:$A$99999),0)),INDEX(last_week_pivot_inven!$A$5:$A$99999,MATCH(0,COUNTIF('sub_total'!A$2:A2,last_week_pivot_inven!$A$5:$A$99999),0)))

 

[Yossi]

Excel 2010