Lookup multi criteria
 

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.

Lookup multi criteria
 

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

Lookup multi criteria
 

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" wrote:

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

Lookup multi criteria
 

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.

"Dave Peterson" wrote:

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


--

Dave Peterson

Lookup multi criteria
 

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.

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson

Lookup multi criteria
 

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" wrote:

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.

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson

Lookup multi criteria
 

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.


oscarcounts wrote:

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" wrote:

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.

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Lookup multi criteria
 

Thanks again, I will try it out tomorrow and compare the speed.

Regards

"Dave Peterson" wrote:

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.


oscarcounts wrote:

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" wrote:

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.

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson