- Joined
- Mar 4, 2008
- Messages
- 1
- Reaction score
- 0
I am using excel 2003.
I have two separate sheets. The 1st sheet is a trial balance which includes ledger codes, department codes and financial values. ie ledger codes in col B, dept codes in col D and financials in col F.
The 2nd sheet obtains data from the 1st sheet by matching ledger code with department code to return a financial value.
I use an INDEX MATCH formula such as the following :
=INDEX($F$2:$F$100,MATCH("A2",IF($D$2:$D$100="C1",$B$2:$B$100),0))
(it would be helpful to avoid the use of parenthesis)
When the ledger code and department code match the data is correct but when there is no match, ie a ledger code does not have a corresponding dept code or vice versa #N/A is returned.
How do I get a zero to replace the #N/A or what other nested formula could I use ?
Many thanks in anticipation.
willienichol
I have two separate sheets. The 1st sheet is a trial balance which includes ledger codes, department codes and financial values. ie ledger codes in col B, dept codes in col D and financials in col F.
The 2nd sheet obtains data from the 1st sheet by matching ledger code with department code to return a financial value.
I use an INDEX MATCH formula such as the following :
=INDEX($F$2:$F$100,MATCH("A2",IF($D$2:$D$100="C1",$B$2:$B$100),0))
(it would be helpful to avoid the use of parenthesis)
When the ledger code and department code match the data is correct but when there is no match, ie a ledger code does not have a corresponding dept code or vice versa #N/A is returned.
How do I get a zero to replace the #N/A or what other nested formula could I use ?
Many thanks in anticipation.
willienichol