Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function problem | Excel Worksheet Functions | |||
Match function problem | Excel Worksheet Functions | |||
MATCH function problem | Excel Discussion (Misc queries) | |||
Problem with match function | Excel Discussion (Misc queries) | |||
Match Function Problem - Won't Find Certain Numbers | Excel Discussion (Misc queries) |