Proble with using match function
 

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.

Proble with using match function
 

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.

Proble with using match function
 

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.

Problem with using match function
 

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.