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.

0
7/20/2005 11:39:47 AM
excel 39879 articles. 2 followers. Follow

1 Replies
730 Views

Similar Articles

[PageSpeed] 49

In Excel's Help for the MATCH function, it describes how the values are 
returned.

Chris wrote:
> 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.
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
7/20/2005 12:30:19 PM
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...

Non-standard behaviour of vertical scroll bar in Folder List
Why, oh why, does the scroll bar in Outlook's Folder List behave differently to any other scroll bar in the Microsoft universe? A scroll bar box, sometimes also called an elevator, will move a screenful when the mouse is clicked in the scroll bar shaft. However, when the mouse click is accompanied by the Shift key, the scroll bar box will move exactly to the position of the click in the scroll bar shaft. This is true for every application which uses the recommended APIs/MFCs. Even Outlook itself does so with the Message List pane and every other scroll bar I could find - except for the F...

How to get resource handle in activex control
Hi, I've got an application which load resources out of a DLL. At the beginning of my application i load my DLL with HINSTANCE hInstance = NULL; hInstance = LoadLibrary( _T("ResZHH.dll") ); AfxSetResourceHandle( hInstance ); So, my application has also an activex control (MyButton.ocx ). This button can show bitmap resources. Now the problem is, that my button shows the bitmap from the application resource and not the bitmap out of my "ResZHH.dll". How can my button get resource information which DLL is loaded? I need the resource handle from the loaded "ResZ...

non ascii characters
I have some old files made in WordStar for windows. If I open them in word pad I can read them except most words end in a non ascii character. I should be able to run that file thru a filter that turns the 8th bit into a 0 but don't know how. Can any suggest a filter? charles You can download the old WordStar filter from my web site. I have not tested with Word 2010 yest, but it works with versions up to 2007. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP M...

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...

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...

shorthand for consistent but non-contiguous range of cells
is there any way of specifying a range of non-contiguous cells? E.g., if I wanted to sum every third cell, rather than going =SUM(A1,A4,A7,A10) is there any notation I could use analogous to =SUM(A1:A10)? I'd prefer to avoid macros in this instance if possible. This would be for a large spreadsheet, to which extra rows are sometimes added. thanks, Julz Here's one way to do it although it's no pleasure to look at. This adds A11, 14, 17 and 20: =SUMPRODUCT(A11:A20,(MOD(ROW(A11:A20)-ROW(A11),3)=0)*1) -- Jim "Julz" <julian@jlittlewood.com> wrote in messag...

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

non owner adding attendees to an activity hangs calendar
Any time we have a non owner of an activity adding a contact as a required or as an optional attendee to an activity in CRM, the calendar and the service calendar both hang - not just for that user, but for the entire organization, until an issreset is performed. Also an error pops up with a generic database fault message, not very descriptive. Previous attempts at stopping/restarting services resulted in hanging services. This seems like a software bug - unhandled permissions error or something maybe? Any thought or ideas are welcome on what we should do to get this issue resolved....

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...

Remove Non-existing Outlook Folder
I have an Archive folder in my folder list that no longer exists. When I try to delete it from the Outlook folder the delete option is not enabled. Any suggestions. What version of Outlook are you using, and if 98 or 2000, what mail support mode? If you're not sure, look at the second line of Help | About Microsoft Outlook -- it should say "Internet Mail Only" or "Corporate/Workgroup". (Outlook 97, 2002, and 2003 don't have separate modes.) -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to th...

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...

How can I make row non-scrollable (the same as header row)
I need to deal with a sheet having more than 150 rows, and I have 50 columns with titles. Please suggest how to make this row with titles non-scrollable, for I could see it even when editing rows at the end of the sheet. -- AkaSaint ------------------------------------------------------------------------ AkaSaint's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28440 View this thread: http://www.excelforum.com/showthread.php?threadid=480399 Select the cell in the first column in the row just below the row you want to remain visible. Then from the Window menu...

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...

Leaking GDI handles
Hi I have a problem with Leaking GDI handles (in a MFC environment mixed with CCoolMenuManager). My goal is to show a zoomed button from the toolbar just to the left of the mouse cursor when the user holds the mouse over the button. When the user moves the cursor, the screen behind the zoomed button must be restored and the zoomed button redrawed at the new position. When the user moves the mouse outside the toolbar, all resources and handles should be released. Any hints? My own implimentation was very bad, but runs until handles are used up. Every Zooming eats up around 4 GDI handles....

Chart non consecutive data
Is it possible to chart non-consecutive data. I haven't been successful in my attempts so far. Data is in same row, ie. row 4; columns needed are H4 and J4 (need to be stacked), next series is K4 , L4 & M4. Unfortunately, I can not rearrange the cells to make this easier. Any help or suggestion would be appreciated. I haven't done that much charting with Excel (2003). TIA XL doesn't stack data in the same series. It stacks one series on top of the previous. You can simulate the effect you want as follows. Select H4:J4 and create a stacked column chart. In step ...

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... >...

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] ...

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 "...

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...

ImageList_AddIcon returning non transparent icon
Hi plz see the code bellow: ------------------------------------------------------------------------------------------- HICON hMotley1 = LoadIcon(_Module.GetResourceInstance(), MAKEINTRESOURCE(IDI_ICON1)); HICON hMotley2 = LoadIcon(_Module.GetResourceInstance(), MAKEINTRESOURCE(IDI_ICON2)); HICON hMotley3 = LoadIcon(_Module.GetResourceInstance(), MAKEINTRESOURCE(IDI_ICON3)); m_hImageList = ImageList_Create(32,16, ILC_COLOR16|ILC_MASK, 0, 8); int iImageIndex = ImageList_AddIcon(m_hImageList, hMotley); DestroyIcon(hMotley); int iImageIndex1 = ImageList_AddIcon(m_hImageList, hMotley1); ...

Filter on non-blanks in a range (multiple columns)
I have a spreadsheet that has offers on parts from multiple clients. The spreadsheet is 12,000 lines long. I am looking to select on a range (of offers) and the highest offer gets put into a BEST OFFER column on the same row. Example Part Number BestOffer Offer#1 Offer#2 Offer#3 Offer#4 Offer#5 xxxxxx-001 $13.00 $1.00 $5.25 $10.00 $3.50 $13.00 xxxxxx-002 $5.00 $1.25 $3.00 $5.00 $4.75 $8.00 The spreadsheet (12,000 lines long) has to remain in the same, original format as it came in BEFORE I received these offers. What is the best bet to address this? Assu...

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...