If(and statements using data in an array
 

I would love some help with the following statement. {=if(and(b2='DBP -
09'!D:D,C2='DBP - 09'!E:E),'DBP - 09'!K:K,0)}. I've previously used excel
97 and am now using excel2000, so not sure if something has changed from that
version to this. The formula returns a #Num error. I've tried every
combination and can't get it to work. The sheet called 'DBP-09' contains
names and salary details. I'm looking up the person's first and last name
and returning the salary value for that person (obviously,where there's a
match found). Help greatly appreciated!!

If(and statements using data in an array
 

You don't need the curly brackets.

"Lou H" wrote:

I would love some help with the following statement. {=if(and(b2='DBP -
09'!D:D,C2='DBP - 09'!E:E),'DBP - 09'!K:K,0)}. I've previously used excel
97 and am now using excel2000, so not sure if something has changed from that
version to this. The formula returns a #Num error. I've tried every
combination and can't get it to work. The sheet called 'DBP-09' contains
names and salary details. I'm looking up the person's first and last name
and returning the salary value for that person (obviously,where there's a
match found). Help greatly appreciated!!

If(and statements using data in an array
 

Just tried it and it doesn't work although its now returning 0 rather than
#NUM - would it something to do with the lookup data being text ?

"Joel" wrote:

You don't need the curly brackets.

"Lou H" wrote:

I would love some help with the following statement. {=if(and(b2='DBP -
09'!D:D,C2='DBP - 09'!E:E),'DBP - 09'!K:K,0)}. I've previously used excel
97 and am now using excel2000, so not sure if something has changed from that
version to this. The formula returns a #Num error. I've tried every
combination and can't get it to work. The sheet called 'DBP-09' contains
names and salary details. I'm looking up the person's first and last name
and returning the salary value for that person (obviously,where there's a
match found). Help greatly appreciated!!

If(and statements using data in an array
 

The formula is working, yo ujust aren't getting the results you expect. I
debug formulas using the Evalute formula tool. Select thhe cells with the
formula then use menu

Worksheet Menu - Tools - Formula Auditing - Evaluate Formula

"Lou H" wrote:

Just tried it and it doesn't work although its now returning 0 rather than
#NUM - would it something to do with the lookup data being text ?

"Joel" wrote:

You don't need the curly brackets.

"Lou H" wrote:

I would love some help with the following statement. {=if(and(b2='DBP -
09'!D:D,C2='DBP - 09'!E:E),'DBP - 09'!K:K,0)}. I've previously used excel
97 and am now using excel2000, so not sure if something has changed from that
version to this. The formula returns a #Num error. I've tried every
combination and can't get it to work. The sheet called 'DBP-09' contains
names and salary details. I'm looking up the person's first and last name
and returning the salary value for that person (obviously,where there's a
match found). Help greatly appreciated!!

If(and statements using data in an array
 

Would that be an add-in option?

"Joel" wrote:

The formula is working, yo ujust aren't getting the results you expect. I
debug formulas using the Evalute formula tool. Select thhe cells with the
formula then use menu

Worksheet Menu - Tools - Formula Auditing - Evaluate Formula

"Lou H" wrote:

Just tried it and it doesn't work although its now returning 0 rather than
#NUM - would it something to do with the lookup data being text ?

"Joel" wrote:

You don't need the curly brackets.

"Lou H" wrote:

I would love some help with the following statement. {=if(and(b2='DBP -
09'!D:D,C2='DBP - 09'!E:E),'DBP - 09'!K:K,0)}. I've previously used excel
97 and am now using excel2000, so not sure if something has changed from that
version to this. The formula returns a #Num error. I've tried every
combination and can't get it to work. The sheet called 'DBP-09' contains
names and salary details. I'm looking up the person's first and last name
and returning the salary value for that person (obviously,where there's a
match found). Help greatly appreciated!!

If(and statements using data in an array
 

No. It is just a tool for debugging a worksheet.

"Lou H" wrote:

Would that be an add-in option?

"Joel" wrote:

The formula is working, yo ujust aren't getting the results you expect. I
debug formulas using the Evalute formula tool. Select thhe cells with the
formula then use menu

Worksheet Menu - Tools - Formula Auditing - Evaluate Formula

"Lou H" wrote:

Just tried it and it doesn't work although its now returning 0 rather than
#NUM - would it something to do with the lookup data being text ?

"Joel" wrote:

You don't need the curly brackets.

"Lou H" wrote:

I would love some help with the following statement. {=if(and(b2='DBP -
09'!D:D,C2='DBP - 09'!E:E),'DBP - 09'!K:K,0)}. I've previously used excel
97 and am now using excel2000, so not sure if something has changed from that
version to this. The formula returns a #Num error. I've tried every
combination and can't get it to work. The sheet called 'DBP-09' contains
names and salary details. I'm looking up the person's first and last name
and returning the salary value for that person (obviously,where there's a
match found). Help greatly appreciated!!