I have made a year wise data base in different sheets like
A B C
D E
1 Months Bill Amount Received Amount Due / Advance Remark
2 Apr'04 45,342.50 32,248.00 13,094.50
Due
3 May'04 62,428.00 68,685.00 6,257.00
Advance
to be continue.... end of month
I have made also a report sheet to see details report (bill amount, receive
amount, etc) of a period (as per choise) by puting moth & year in cell A2. So
I have used a formula in B2 as below :-
=if(match(a2,Yr_0405,0),vlookup(a2,Yr_0405,2,0),if (match(a2,Yr_0506,0),vlookup(a2,Yr_0506,2,0),""))
Result show "#N/A,
Note = Yr_0405 is refrernce of Year 0405 data base
Yr_0506 is reference of Year 0506 data base
what should I do to see the report, help me.

The MATCH function is returning either an index (if it matches) or NA. But
the condition in the IF function should be TRUE/FALSE. So enclose the match
in an ISNUMBER test:
=if(isnumber(match(a2,Yr_0405,0)),vlookup(a2,Yr_04 05,2,0),if(isnumber(match(a2,Yr_0506,0)),vlookup(a 2,Yr_0506,2,0),""))


"Montu" wrote:

I have made a year wise data base in different sheets like
A B C
D E
1 Months Bill Amount Received Amount Due / Advance Remark
2 Apr'04 45,342.50 32,248.00 13,094.50
Due
3 May'04 62,428.00 68,685.00 6,257.00
Advance
to be continue.... end of month
I have made also a report sheet to see details report (bill amount, receive
amount, etc) of a period (as per choise) by puting moth & year in cell A2. So
I have used a formula in B2 as below :-
=if(match(a2,Yr_0405,0),vlookup(a2,Yr_0405,2,0),if (match(a2,Yr_0506,0),vlookup(a2,Yr_0506,2,0),""))
Result show "#N/A,
Note = Yr_0405 is refrernce of Year 0405 data base
Yr_0506 is reference of Year 0506 data base
what should I do to see the report, help me.

It looks like Yr_0405 and Yr_0506 are 2 dimensional arrays since you're
using them in VLOOKUP and specifying the column_index_number of 2. You're
also referencing these same named ranges in MATCH but MATCH can only work on
a one dimensional array as the lookup_array.

Try it like this:

=IF(COUNTIF(INDEX(Yr_0405,,1),A2),VLOOKUP(A2,Yr_04 05,2,0),IF(COUNTIF(INDEX(Yr_0506,,1),A2),VLOOKUP(A 2,Yr_0506,2,0),""))

--
Biff
Microsoft Excel MVP


"Montu" wrote in message
...
I have made a year wise data base in different sheets like
A B C
D E
1 Months Bill Amount Received Amount Due / Advance
Remark
2 Apr'04 45,342.50 32,248.00 13,094.50
Due
3 May'04 62,428.00 68,685.00 6,257.00
Advance
to be continue.... end of month
I have made also a report sheet to see details report (bill amount,
receive
amount, etc) of a period (as per choise) by puting moth & year in cell A2.
So
I have used a formula in B2 as below :-
=if(match(a2,Yr_0405,0),vlookup(a2,Yr_0405,2,0),if (match(a2,Yr_0506,0),vlookup(a2,Yr_0506,2,0),""))
Result show "#N/A,
Note = Yr_0405 is refrernce of Year 0405 data base
Yr_0506 is reference of Year 0506 data base
what should I do to see the report, help me.

Thank you for your valuable suggestion, it's working too good. But there is a
little bit problem I found. Actually the fault was in my site. I was forget
to write about my quary. So beg excuse for this reason. Now I'm writing about
my quaries - I have entered an extra row before the month cell (perv. A2, now
A3) in report sheet. Now I want that, if I write moth in the proper cell (A3)
the previous cell show the opeing amout of the write month.
supose in A3 choose Jun-06 then A2 show the closing balance of May-06. So is
it possible ? pls help me.

"T. Valko" wrote:

It looks like Yr_0405 and Yr_0506 are 2 dimensional arrays since you're
using them in VLOOKUP and specifying the column_index_number of 2. You're
also referencing these same named ranges in MATCH but MATCH can only work on
a one dimensional array as the lookup_array.

Try it like this:

=IF(COUNTIF(INDEX(Yr_0405,,1),A2),VLOOKUP(A2,Yr_04 05,2,0),IF(COUNTIF(INDEX(Yr_0506,,1),A2),VLOOKUP(A 2,Yr_0506,2,0),""))

--
Biff
Microsoft Excel MVP


"Montu" wrote in message
...
I have made a year wise data base in different sheets like
A B C
D E
1 Months Bill Amount Received Amount Due / Advance
Remark
2 Apr'04 45,342.50 32,248.00 13,094.50
Due
3 May'04 62,428.00 68,685.00 6,257.00
Advance
to be continue.... end of month
I have made also a report sheet to see details report (bill amount,
receive
amount, etc) of a period (as per choise) by puting moth & year in cell A2.
So
I have used a formula in B2 as below :-
=if(match(a2,Yr_0405,0),vlookup(a2,Yr_0405,2,0),if (match(a2,Yr_0506,0),vlookup(a2,Yr_0506,2,0),""))
Result show "#N/A,
Note = Yr_0405 is refrernce of Year 0405 data base
Yr_0506 is reference of Year 0506 data base
what should I do to see the report, help me.