Problem 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 Opening
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 A3. So
I had request to forum to as per my quiry & I got answer from here as
=IF(COUNTIF(INDEX(Yr_0405,,1),A3),VLOOKUP(A3,Yr_04 05,2,0),IF(COUNTIF(INDEX(Yr_0506,,1),A3),VLOOKUP(A 3,Yr_0506,2,0),"")).
It's working good but there is little bit problem that is suppose if I choos
Jun-04 in A3 of report sheet then in A2 show the closing balance of the
previous month (may-04). so would it possible. help me & thanks in advance.
Note = Yr_0405 is refrernce of Year 0405 data base
Yr_0506 is reference of Year 0506 data base

Problem with using match function
 

This sounds like a nightmare!

If the "dates" are in sequential order then you can use something like this:

=INDEX(range_of_value_to_return,MATCH(date,date_ra nge,0)+/-1)

The +/-1 depends on what order the dates are in. If they're in ascending
order then you would use -1. If the dates are in descending order then you
would use +1.

If the dates are in random order this becomes much more complicated. If you
need to "search" 2 different sheets for the date then this becomes a
nightmare!

--
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 Opening
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 A3.
So
I had request to forum to as per my quiry & I got answer from here as
=IF(COUNTIF(INDEX(Yr_0405,,1),A3),VLOOKUP(A3,Yr_04 05,2,0),IF(COUNTIF(INDEX(Yr_0506,,1),A3),VLOOKUP(A 3,Yr_0506,2,0),"")).
It's working good but there is little bit problem that is suppose if I
choos
Jun-04 in A3 of report sheet then in A2 show the closing balance of the
previous month (may-04). so would it possible. help me & thanks in
advance.
Note = Yr_0405 is refrernce of Year 0405 data base
Yr_0506 is reference of Year 0506 data base