| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statements in Data Validation | Excel Worksheet Functions | |||
| Data Validation - IF statements | New Users to Excel | |||
| IF statements and moving data to different columns | Excel Worksheet Functions | |||
| Data Validation w/ If, Match & Index Statements | Excel Worksheet Functions | |||
| Problem with data using IF and Nested IF statements possibly??? | Excel Discussion (Misc queries) | |||
Linear Mode
