Match Function

Hi,
I have put together a spreadsheet to find codes, states, descriptions.  It 
finds the data by dates.  
Columns are: Dates, Codes, States, Descriptions
If I put in a date it matches the code, state and description.

My problem is that if I have 2 dates that are the same it will only match 
the first date it can find.  What kind of formula do I need so that it will 
output the second or third date data.

Thanks for your help.
If you need a copy of the spreadsheet please let me know.

0
Melanie (39)
6/16/2009 4:29:01 AM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
1173 Views

Similar Articles

[PageSpeed] 45

How many rows of data do you have?

-- 
Biff
Microsoft Excel MVP


"Melanie" <Melanie@discussions.microsoft.com> wrote in message 
news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
> Hi,
> I have put together a spreadsheet to find codes, states, descriptions.  It
> finds the data by dates.
> Columns are: Dates, Codes, States, Descriptions
> If I put in a date it matches the code, state and description.
>
> My problem is that if I have 2 dates that are the same it will only match
> the first date it can find.  What kind of formula do I need so that it 
> will
> output the second or third date data.
>
> Thanks for your help.
> If you need a copy of the spreadsheet please let me know.
> 


0
biffinpitt (3172)
6/16/2009 5:11:17 AM
Lots - over 300 at the moment and it gets added to all the time. 

"T. Valko" wrote:

> How many rows of data do you have?
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Melanie" <Melanie@discussions.microsoft.com> wrote in message 
> news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
> > Hi,
> > I have put together a spreadsheet to find codes, states, descriptions.  It
> > finds the data by dates.
> > Columns are: Dates, Codes, States, Descriptions
> > If I put in a date it matches the code, state and description.
> >
> > My problem is that if I have 2 dates that are the same it will only match
> > the first date it can find.  What kind of formula do I need so that it 
> > will
> > output the second or third date data.
> >
> > Thanks for your help.
> > If you need a copy of the spreadsheet please let me know.
> > 
> 
> 
> 
0
Melanie (39)
6/16/2009 5:35:01 AM
One way...

Assume your data is in the range A2:D500

F2:H2 = headers: Code, State, Description

F1 = lookup date

Enter this formula in G1. This will return the count of dates based on the 
date entered in cell F1:

=COUNTIF(A:A,F1)

Enter this array formula** in F3:

=IF(ROWS(F$3:F3)<=$G$1,INDEX(B$2:B$500,SMALL(IF($A$2:$A$500=$F$1,ROW(B$2:B$500)),ROWS(F$3:F3))-ROW(B$2)+1),"")

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

Copy across to H3 then down to a number of rows that ensures you get all the 
results. How many rows that is depends on the maximum count for any single 
date. For example, if 1/1/2009 appears the most times, let's say it appears 
5 times, then you need to copy the formula to at least 5 rows.

-- 
Biff
Microsoft Excel MVP


"Melanie" <Melanie@discussions.microsoft.com> wrote in message 
news:7F92CE7F-AF22-4DD8-A9D8-F302CAAD1E38@microsoft.com...
> Lots - over 300 at the moment and it gets added to all the time.
>
> "T. Valko" wrote:
>
>> How many rows of data do you have?
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
>> news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
>> > Hi,
>> > I have put together a spreadsheet to find codes, states, descriptions. 
>> > It
>> > finds the data by dates.
>> > Columns are: Dates, Codes, States, Descriptions
>> > If I put in a date it matches the code, state and description.
>> >
>> > My problem is that if I have 2 dates that are the same it will only 
>> > match
>> > the first date it can find.  What kind of formula do I need so that it
>> > will
>> > output the second or third date data.
>> >
>> > Thanks for your help.
>> > If you need a copy of the spreadsheet please let me know.
>> >
>>
>>
>> 


0
biffinpitt (3172)
6/16/2009 6:03:16 AM
I put the formulas in and the count works but the second formula gives me a 
#Value Error.

"T. Valko" wrote:

> One way...
> 
> Assume your data is in the range A2:D500
> 
> F2:H2 = headers: Code, State, Description
> 
> F1 = lookup date
> 
> Enter this formula in G1. This will return the count of dates based on the 
> date entered in cell F1:
> 
> =COUNTIF(A:A,F1)
> 
> Enter this array formula** in F3:
> 
> =IF(ROWS(F$3:F3)<=$G$1,INDEX(B$2:B$500,SMALL(IF($A$2:$A$500=$F$1,ROW(B$2:B$500)),ROWS(F$3:F3))-ROW(B$2)+1),"")
> 
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
> key then hit ENTER.
> 
> Copy across to H3 then down to a number of rows that ensures you get all the 
> results. How many rows that is depends on the maximum count for any single 
> date. For example, if 1/1/2009 appears the most times, let's say it appears 
> 5 times, then you need to copy the formula to at least 5 rows.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Melanie" <Melanie@discussions.microsoft.com> wrote in message 
> news:7F92CE7F-AF22-4DD8-A9D8-F302CAAD1E38@microsoft.com...
> > Lots - over 300 at the moment and it gets added to all the time.
> >
> > "T. Valko" wrote:
> >
> >> How many rows of data do you have?
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
> >> news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
> >> > Hi,
> >> > I have put together a spreadsheet to find codes, states, descriptions. 
> >> > It
> >> > finds the data by dates.
> >> > Columns are: Dates, Codes, States, Descriptions
> >> > If I put in a date it matches the code, state and description.
> >> >
> >> > My problem is that if I have 2 dates that are the same it will only 
> >> > match
> >> > the first date it can find.  What kind of formula do I need so that it
> >> > will
> >> > output the second or third date data.
> >> >
> >> > Thanks for your help.
> >> > If you need a copy of the spreadsheet please let me know.
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Melanie (39)
6/16/2009 6:34:03 AM
Did you array enter it as suggested?

-- 
__________________________________
HTH

Bob

"Melanie" <Melanie@discussions.microsoft.com> wrote in message 
news:D175F9D2-D404-46C5-82A5-8C68D1ACAD40@microsoft.com...
>I put the formulas in and the count works but the second formula gives me a
> #Value Error.
>
> "T. Valko" wrote:
>
>> One way...
>>
>> Assume your data is in the range A2:D500
>>
>> F2:H2 = headers: Code, State, Description
>>
>> F1 = lookup date
>>
>> Enter this formula in G1. This will return the count of dates based on 
>> the
>> date entered in cell F1:
>>
>> =COUNTIF(A:A,F1)
>>
>> Enter this array formula** in F3:
>>
>> =IF(ROWS(F$3:F3)<=$G$1,INDEX(B$2:B$500,SMALL(IF($A$2:$A$500=$F$1,ROW(B$2:B$500)),ROWS(F$3:F3))-ROW(B$2)+1),"")
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
>> SHIFT
>> key then hit ENTER.
>>
>> Copy across to H3 then down to a number of rows that ensures you get all 
>> the
>> results. How many rows that is depends on the maximum count for any 
>> single
>> date. For example, if 1/1/2009 appears the most times, let's say it 
>> appears
>> 5 times, then you need to copy the formula to at least 5 rows.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
>> news:7F92CE7F-AF22-4DD8-A9D8-F302CAAD1E38@microsoft.com...
>> > Lots - over 300 at the moment and it gets added to all the time.
>> >
>> > "T. Valko" wrote:
>> >
>> >> How many rows of data do you have?
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
>> >> news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
>> >> > Hi,
>> >> > I have put together a spreadsheet to find codes, states, 
>> >> > descriptions.
>> >> > It
>> >> > finds the data by dates.
>> >> > Columns are: Dates, Codes, States, Descriptions
>> >> > If I put in a date it matches the code, state and description.
>> >> >
>> >> > My problem is that if I have 2 dates that are the same it will only
>> >> > match
>> >> > the first date it can find.  What kind of formula do I need so that 
>> >> > it
>> >> > will
>> >> > output the second or third date data.
>> >> >
>> >> > Thanks for your help.
>> >> > If you need a copy of the spreadsheet please let me know.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>> 


0
BobNGs (423)
6/16/2009 11:30:33 AM
I have now.  And it is now working.
Thank you so much for all your help.

"Bob Phillips" wrote:

> Did you array enter it as suggested?
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Melanie" <Melanie@discussions.microsoft.com> wrote in message 
> news:D175F9D2-D404-46C5-82A5-8C68D1ACAD40@microsoft.com...
> >I put the formulas in and the count works but the second formula gives me a
> > #Value Error.
> >
> > "T. Valko" wrote:
> >
> >> One way...
> >>
> >> Assume your data is in the range A2:D500
> >>
> >> F2:H2 = headers: Code, State, Description
> >>
> >> F1 = lookup date
> >>
> >> Enter this formula in G1. This will return the count of dates based on 
> >> the
> >> date entered in cell F1:
> >>
> >> =COUNTIF(A:A,F1)
> >>
> >> Enter this array formula** in F3:
> >>
> >> =IF(ROWS(F$3:F3)<=$G$1,INDEX(B$2:B$500,SMALL(IF($A$2:$A$500=$F$1,ROW(B$2:B$500)),ROWS(F$3:F3))-ROW(B$2)+1),"")
> >>
> >> ** array formulas need to be entered using the key combination of
> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> >> SHIFT
> >> key then hit ENTER.
> >>
> >> Copy across to H3 then down to a number of rows that ensures you get all 
> >> the
> >> results. How many rows that is depends on the maximum count for any 
> >> single
> >> date. For example, if 1/1/2009 appears the most times, let's say it 
> >> appears
> >> 5 times, then you need to copy the formula to at least 5 rows.
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
> >> news:7F92CE7F-AF22-4DD8-A9D8-F302CAAD1E38@microsoft.com...
> >> > Lots - over 300 at the moment and it gets added to all the time.
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> How many rows of data do you have?
> >> >>
> >> >> -- 
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
> >> >> news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
> >> >> > Hi,
> >> >> > I have put together a spreadsheet to find codes, states, 
> >> >> > descriptions.
> >> >> > It
> >> >> > finds the data by dates.
> >> >> > Columns are: Dates, Codes, States, Descriptions
> >> >> > If I put in a date it matches the code, state and description.
> >> >> >
> >> >> > My problem is that if I have 2 dates that are the same it will only
> >> >> > match
> >> >> > the first date it can find.  What kind of formula do I need so that 
> >> >> > it
> >> >> > will
> >> >> > output the second or third date data.
> >> >> >
> >> >> > Thanks for your help.
> >> >> > If you need a copy of the spreadsheet please let me know.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Melanie (39)
6/17/2009 2:44:01 AM
Glad you got it straightened out! Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Melanie" <Melanie@discussions.microsoft.com> wrote in message 
news:A3A8BB5F-280A-4E7E-8DBA-C16E1BC10E4B@microsoft.com...
>I have now.  And it is now working.
> Thank you so much for all your help.
>
> "Bob Phillips" wrote:
>
>> Did you array enter it as suggested?
>>
>> -- 
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
>> news:D175F9D2-D404-46C5-82A5-8C68D1ACAD40@microsoft.com...
>> >I put the formulas in and the count works but the second formula gives 
>> >me a
>> > #Value Error.
>> >
>> > "T. Valko" wrote:
>> >
>> >> One way...
>> >>
>> >> Assume your data is in the range A2:D500
>> >>
>> >> F2:H2 = headers: Code, State, Description
>> >>
>> >> F1 = lookup date
>> >>
>> >> Enter this formula in G1. This will return the count of dates based on
>> >> the
>> >> date entered in cell F1:
>> >>
>> >> =COUNTIF(A:A,F1)
>> >>
>> >> Enter this array formula** in F3:
>> >>
>> >> =IF(ROWS(F$3:F3)<=$G$1,INDEX(B$2:B$500,SMALL(IF($A$2:$A$500=$F$1,ROW(B$2:B$500)),ROWS(F$3:F3))-ROW(B$2)+1),"")
>> >>
>> >> ** array formulas need to be entered using the key combination of
>> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> >> SHIFT
>> >> key then hit ENTER.
>> >>
>> >> Copy across to H3 then down to a number of rows that ensures you get 
>> >> all
>> >> the
>> >> results. How many rows that is depends on the maximum count for any
>> >> single
>> >> date. For example, if 1/1/2009 appears the most times, let's say it
>> >> appears
>> >> 5 times, then you need to copy the formula to at least 5 rows.
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
>> >> news:7F92CE7F-AF22-4DD8-A9D8-F302CAAD1E38@microsoft.com...
>> >> > Lots - over 300 at the moment and it gets added to all the time.
>> >> >
>> >> > "T. Valko" wrote:
>> >> >
>> >> >> How many rows of data do you have?
>> >> >>
>> >> >> -- 
>> >> >> Biff
>> >> >> Microsoft Excel MVP
>> >> >>
>> >> >>
>> >> >> "Melanie" <Melanie@discussions.microsoft.com> wrote in message
>> >> >> news:90BFB466-5D47-41E9-BBD0-97F5843E5126@microsoft.com...
>> >> >> > Hi,
>> >> >> > I have put together a spreadsheet to find codes, states,
>> >> >> > descriptions.
>> >> >> > It
>> >> >> > finds the data by dates.
>> >> >> > Columns are: Dates, Codes, States, Descriptions
>> >> >> > If I put in a date it matches the code, state and description.
>> >> >> >
>> >> >> > My problem is that if I have 2 dates that are the same it will 
>> >> >> > only
>> >> >> > match
>> >> >> > the first date it can find.  What kind of formula do I need so 
>> >> >> > that
>> >> >> > it
>> >> >> > will
>> >> >> > output the second or third date data.
>> >> >> >
>> >> >> > Thanks for your help.
>> >> >> > If you need a copy of the spreadsheet please let me know.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
6/17/2009 3:00:32 AM
Reply:

Similar Artilces:

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Compact folder function doesn't appear to work
On two different machines, one with XP and OL 2002 and the other W2K with OL 2000. When I perform the compact function it flashes the little message Compacting... for about 2 seconds. When I check the file size it does not change, even though I've deleted or archived at least 1-2 Mb worth of messages (in several cases, quite a bit more. I typically use shift-delete, so it is not as if the stuff is sitting in my deleted items folder. I use F5 to refresh my view of the directory that it is in, I see the file date/time update, but no change in the file size, none. I even exit OL, still no ch...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Trim function #3
In cell A2, I use the trim function to trim cell A1 containing a date eg 1202/09, the result in A2 is 39856. However, this 39856 is text and not date value. I therefore cannot convert it back to date after the trimming. Can someone please help? Many thanks... -- exalan Just format the cell as date to display: 2/12/2009 -- Gary''s Student - gsnu200833 On Feb 14, 7:17=A0am, exalan <exa...@discussions.microsoft.com> wrote: > In cell A2, I use the trim function to trim cell > A1 containing a date eg 1202/09, the result in A2 > is 39856. However, this 39856 is text an...

IF function + Wildcard question
I'm trying to locate any and all email addresses in column A using the IF function and the wildcard (*) =IF(A1="*@*", TRUE, FALSE) However, all function results appear as "FALSE" when it should be "TRUE" Any suggestions? Thanks in advance and Happy Holidays. --- Message posted from http://www.ExcelForum.com/ Try this formula =NOT(ISERROR(FIND("@",A2)>0)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "illhawaiian" <illhawaiian.z9khy@...

Countif function in the same row but in alter...
I am trying to carry out a countif function in alternate cells. For example count the number of cells contaning only the letter A in cells A1, A3, A5, A7 etc..Similarly for the letters B, C and D. John, Try this SUMPRODUCT formula =SUMPRODUCT(--(A1:A10="A"),--(MOD(ROW($A$1:$A$10)-CELL("row",$A$1:$A$10),2)= 0)) -- HTH RP "John@finland" <John@finland@discussions.microsoft.com> wrote in message news:E4D03A79-B98B-451F-BA9C-96557B2E39F2@microsoft.com... > I am trying to carry out a countif function in alternate cells. For example > count the num...

How to check if a certain function is used
Hi What's the best way to check if a certain spreadsheet function is used in a cell? For instance, we want to check if cell A1 uses SUM function considering that A1 may contain: 1. A1 ="Sum" 2. A1 =SUM(B1:B2) 3. A1 =If(A1="Sum","True",SUM(B1:B2)) These is not a complete list of possibilities. I just wanted to show that search a formula string for certain words is not the best solution. Any ideas? Kind regards IgorM Sub dk() MsgBox Range("B2").Formula End Sub Will give you the formula used in B2. "IgorM" &...

Loss of functionality
I installed a copy of Office 2000 pro on my new machine and I did all the updates for it and lost some email functionality. On the old machine, same version of office 9.0.0.6627 SP-3, I was able to set email properties in the contact window, for ex. Display Name, email address, and email type. On the new machine I am not able to same version 9.0.0.6627 SP-3. Is there something wrong or something I am missing. Is there an update someone knows about that i dont? How about an add-on, i tried several? ...

how to add ( and ) when using function sum
want to add 1+2+3 = (6) how do to it? =SUM(1,2,3) -- Regards, Peo Sjoblom "Sulasno" <sulasnoXXX@hotmail.com> wrote in message news:%23CbFRWW8HHA.5360@TK2MSFTNGP03.phx.gbl... > want to add 1+2+3 = (6) > > how do to it? > =sum(1,2,3) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Sulasno" <sulasnoXXX@hotmail.com> wrote in message news:%23CbFRWW8HHA.5360@TK2MSFTNGP03.phx.gbl... > want to add 1+2+3 = (6) > > how do to it? > Hi Sulasno, A few different ways, In cell A1 type =1+2+3 a...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Outlook crmaddin often does not function 01-29-07
Hello, In one of my installations, users need to remove and add the crmaddin quite often at their outlook clients. Most of them have the desktop version. Do you know if there is a hotfix/patch available that makes the crmaddin more stable? Can you advise? Thank you, Elena If you are running V3.0, you may see some improvements in upgrading to V3C. While V3C is a compatibility update to support Vista and Office 2007, it also works with Office XP and 2003. While there are still problems (I see the posts on this news group), I believe V3C is a more stable client. The V3C client does ...

XIRR Function Using Months Instead of Years
I am trying to modify a model that uses the XIRR function. Instead of breaking revenue and costs down by year, I want to do it by month. The result of the XIRR function when I do that, though, is overstated quite a bit. Any ideas what I am doing wrong? Thanks! Do you have a list of dates for you cash flow? And are they dates? From help: "Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text." HTH, Bernie MS Excel MVP ...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

Index, Match, Min and Max question
I'm trying to retrieve a date (in column A) that corresponds with a Min and Max amount (in columns B:D). Basically I need to know when my amounts hit their Highs and Lows. Can someone help me with this please? Thanks, Maria Dates in column A; values in B Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0)) Returns the date corresponding the max value in B Is this what was needed? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email <mgriffiths@klmtel.net> wrote in message news:1194464476.732636.234500@50g2000hsm.googlegroups.com... >...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

F9 function key
Hi, Today I am learning F9. From Help:- =20 "F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array = formulas) calculates the selected portion of a formula and replaces the = selected portion with the calculated value." This is great. If formula auditing fails me, I can use F9. I find that = if I highlight part of a formula and press F9 **without** pressing = enter, it still works. My question is this:- After the partial formula has been replaced by a value, how do I reverse = back to the original formula with arguments? What shortcut key do I = use? Do I have to ...

HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical)
HOT Reqs : Microsoft Business Solutions | 12+ Months | Chicago, IL (Functional and technical) ---------------------------------------------------------------------------------------------------------------------------------------------- Microsoft Business Solutions (Microsoft Small Business Financials) Need one resource each for Functional and Technical roles. Location : Chicago, IL Duration : 12+ Months Funtional : This person will be responsible for the requirements gathering and setting up the system. Technical : This person will be responsible for installing and maintaining th...

version matching
Do both people have to have the same version of Outlook running in order for Tasks and Appointments to sync up properly when emailed between clients? I'm seeing some irregularities between someone running Outlook in Office 2000 and mine which is part of Office 2003. Appointments with reminders sent from Office 2002 or later will show up as text in Outlook 2000 unless a hotfix is applied to the Outlook 2000 installation. Besides this issue, I am not aware of any others except for ..pst file type incompatibilities, easily solved on the 2003 side. -- Milly Staples [MVP - Outlook] ...

fuzzy logic matching in Excel
Is there such a thing as fuzzy logic matching in excel? For example, if I have Name Addr1 Addr2 Addr3 Davie Kings Road London England and Davie Kings Road - London This should match as a potential pair. How can I do this in Excel? Any ideas? Nothing built in. Davie wrote: > > Is there such a thing as fuzzy logic matching in excel? > > For example, if I have > Name Addr1 Addr2 Addr3 > Davie Kings Road London Eng...

Offset match formula
Hi, I have the following offset match formula that I use to correctly pull data into a summary sheet by date and metric name. Every day I update my week and it pulls in the new data corrrectly. Is it possible to add to this formula so that I can sum range of dates in my source sheet? For example, I have a YTD total that I would like to be able to use this formula for. But in order to do that I need to adjust it so that it will sum up all of the rows between two dates. Is this possible? What would the formula look like? Do I need to use a different formula? =OFFSET(Source!$F$118:$ES$201...