Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 50
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match function problem SJC Excel Worksheet Functions 3 September 14th 07 06:02 PM
Match function problem [email protected] Excel Worksheet Functions 3 October 4th 06 07:39 PM
MATCH function problem LACA Excel Discussion (Misc queries) 10 May 22nd 06 01:39 AM
Problem with match function nander Excel Discussion (Misc queries) 4 April 18th 06 10:08 PM
Match Function Problem - Won't Find Certain Numbers PE Excel Discussion (Misc queries) 2 May 9th 05 03:53 PM


All times are GMT +1. The time now is 03:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"