Many thanks for your e-mail and formula. This is the formula that I am using
in the 2nd workbook which works when the three critreia and financial value
match. However when there is no match #N/A is returned. How can I replace the
#N/A with a 0 (zero) ?
=INDEX('[RBAS Trial balance.xls]TOTALS'!$F$2:$F$2520,MATCH(1,($A8='[RBAS
Trial balance.xls]TOTALS'!$B$2:$B$2520)*("Q02"='[RBAS Trial
balance.xls]TOTALS'!$C$2:$C$2520)*($C$3='[RBAS Trial
balance.xls]TOTALS'!$D$2:$D$2520),0))
Workbook 1. (Financial Trial balance)
Col B Codes (say 60001)
Col C Cost Centres ( say Q02)
Col D Departments ( say 801)
Col F Financial values ( say £100)
Workbook 2
The purpose is to obtain data from Col F in WB 1 when Code (col B), Cost
Centre (col C) and Dept (col D ) match. If there is no match a 0 (zero)
should be returned.
I hope this makes sense!
Kind regards.
Dave Peterson said:Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
Dave said:If you're using xl2007, look at =iferror() in Excel's help.
If you're xl2003 or below, then you could double up the formula:
=if(isna(thatlongformula),0,thatlongformula)
Personally, I'd use a couple of columns (say B and C):
The first one is just that formula
The second one is:
=if(isna(b2),0,b2)
and I'd hide column B.
Depending on how many rows are in the table and how many formulas you're going
to use, doubling up the formulas could really slow calculations down.
Many thanks for your e-mail and formula. This is the formula that I am using
in the 2nd workbook which works when the three critreia and financial value
match. However when there is no match #N/A is returned. How can I replace the
#N/A with a 0 (zero) ?
=INDEX('[RBAS Trial balance.xls]TOTALS'!$F$2:$F$2520,MATCH(1,($A8='[RBAS
Trial balance.xls]TOTALS'!$B$2:$B$2520)*("Q02"='[RBAS Trial
balance.xls]TOTALS'!$C$2:$C$2520)*($C$3='[RBAS Trial
balance.xls]TOTALS'!$D$2:$D$2520),0))
Workbook 1. (Financial Trial balance)
Col B Codes (say 60001)
Col C Cost Centres ( say Q02)
Col D Departments ( say 801)
Col F Financial values ( say £100)
Workbook 2
The purpose is to obtain data from Col F in WB 1 when Code (col B), Cost
Centre (col C) and Dept (col D ) match. If there is no match a 0 (zero)
should be returned.
I hope this makes sense!
Kind regards.
Dave Peterson said:Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
oscarcounts wrote:
I have two sheets. The second sheet obtains data from the first sheet. The
value (£) depends on three criteria in the same row. ie Code,Cost centre and
Department.
When all three match the value (£) is returned or if not 0.
Dave Peterson said:Actually, you'd only need to check for an error with this portion:
=if(isna(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0)),0,longformulahere)
Dave said:If you're using xl2007, look at =iferror() in Excel's help.
If you're xl2003 or below, then you could double up the formula:
=if(isna(thatlongformula),0,thatlongformula)
Personally, I'd use a couple of columns (say B and C):
The first one is just that formula
The second one is:
=if(isna(b2),0,b2)
and I'd hide column B.
Depending on how many rows are in the table and how many formulas you're going
to use, doubling up the formulas could really slow calculations down.
Many thanks for your e-mail and formula. This is the formula that I am using
in the 2nd workbook which works when the three critreia and financial value
match. However when there is no match #N/A is returned. How can I replace the
#N/A with a 0 (zero) ?
=INDEX('[RBAS Trial balance.xls]TOTALS'!$F$2:$F$2520,MATCH(1,($A8='[RBAS
Trial balance.xls]TOTALS'!$B$2:$B$2520)*("Q02"='[RBAS Trial
balance.xls]TOTALS'!$C$2:$C$2520)*($C$3='[RBAS Trial
balance.xls]TOTALS'!$D$2:$D$2520),0))
Workbook 1. (Financial Trial balance)
Col B Codes (say 60001)
Col C Cost Centres ( say Q02)
Col D Departments ( say 801)
Col F Financial values ( say £100)
Workbook 2
The purpose is to obtain data from Col F in WB 1 when Code (col B), Cost
Centre (col C) and Dept (col D ) match. If there is no match a 0 (zero)
should be returned.
I hope this makes sense!
Kind regards.
:
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
oscarcounts wrote:
I have two sheets. The second sheet obtains data from the first sheet. The
value (£) depends on three criteria in the same row. ie Code,Cost centre and
Department.
When all three match the value (£) is returned or if not 0.
Useing the two column solution works all OK. I attempted to re-write it in
one cell but have not been successful yet. I would prefer the one cell option
but I can progress my work using this remedy. If you could supply the one
cell option in full that would be great.( I may be entering wrongly!)
Many thanks for your assistance it is greatly appreciated.
Dave Peterson said:Actually, you'd only need to check for an error with this portion:
=if(isna(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0)),0,longformulahere)
Dave said:If you're using xl2007, look at =iferror() in Excel's help.
If you're xl2003 or below, then you could double up the formula:
=if(isna(thatlongformula),0,thatlongformula)
Personally, I'd use a couple of columns (say B and C):
The first one is just that formula
The second one is:
=if(isna(b2),0,b2)
and I'd hide column B.
Depending on how many rows are in the table and how many formulas you're going
to use, doubling up the formulas could really slow calculations down.
oscarcounts wrote:
Many thanks for your e-mail and formula. This is the formula that I am using
in the 2nd workbook which works when the three critreia and financial value
match. However when there is no match #N/A is returned. How can I replace the
#N/A with a 0 (zero) ?
=INDEX('[RBAS Trial balance.xls]TOTALS'!$F$2:$F$2520,MATCH(1,($A8='[RBAS
Trial balance.xls]TOTALS'!$B$2:$B$2520)*("Q02"='[RBAS Trial
balance.xls]TOTALS'!$C$2:$C$2520)*($C$3='[RBAS Trial
balance.xls]TOTALS'!$D$2:$D$2520),0))
Workbook 1. (Financial Trial balance)
Col B Codes (say 60001)
Col C Cost Centres ( say Q02)
Col D Departments ( say 801)
Col F Financial values ( say £100)
Workbook 2
The purpose is to obtain data from Col F in WB 1 when Code (col B), Cost
Centre (col C) and Dept (col D ) match. If there is no match a 0 (zero)
should be returned.
I hope this makes sense!
Kind regards.
:
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
oscarcounts wrote:
I have two sheets. The second sheet obtains data from the first sheet. The
value (£) depends on three criteria in the same row. ie Code,Cost centre and
Department.
When all three match the value (£) is returned or if not 0.
Dave Peterson said:I'd still use two columns to keep the calculations speedy.
=if(isna(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0)),0,
index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0)))
Still an array formula.
Useing the two column solution works all OK. I attempted to re-write it in
one cell but have not been successful yet. I would prefer the one cell option
but I can progress my work using this remedy. If you could supply the one
cell option in full that would be great.( I may be entering wrongly!)
Many thanks for your assistance it is greatly appreciated.
Dave Peterson said:Actually, you'd only need to check for an error with this portion:
=if(isna(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0)),0,longformulahere)
Dave Peterson wrote:
If you're using xl2007, look at =iferror() in Excel's help.
If you're xl2003 or below, then you could double up the formula:
=if(isna(thatlongformula),0,thatlongformula)
Personally, I'd use a couple of columns (say B and C):
The first one is just that formula
The second one is:
=if(isna(b2),0,b2)
and I'd hide column B.
Depending on how many rows are in the table and how many formulas you're going
to use, doubling up the formulas could really slow calculations down.
oscarcounts wrote:
Many thanks for your e-mail and formula. This is the formula that I am using
in the 2nd workbook which works when the three critreia and financial value
match. However when there is no match #N/A is returned. How can I replace the
#N/A with a 0 (zero) ?
=INDEX('[RBAS Trial balance.xls]TOTALS'!$F$2:$F$2520,MATCH(1,($A8='[RBAS
Trial balance.xls]TOTALS'!$B$2:$B$2520)*("Q02"='[RBAS Trial
balance.xls]TOTALS'!$C$2:$C$2520)*($C$3='[RBAS Trial
balance.xls]TOTALS'!$D$2:$D$2520),0))
Workbook 1. (Financial Trial balance)
Col B Codes (say 60001)
Col C Cost Centres ( say Q02)
Col D Departments ( say 801)
Col F Financial values ( say £100)
Workbook 2
The purpose is to obtain data from Col F in WB 1 when Code (col B), Cost
Centre (col C) and Dept (col D ) match. If there is no match a 0 (zero)
should be returned.
I hope this makes sense!
Kind regards.
:
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
oscarcounts wrote:
I have two sheets. The second sheet obtains data from the first sheet. The
value (£) depends on three criteria in the same row. ie Code,Cost centre and
Department.
When all three match the value (£) is returned or if not 0.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.