MATCH Function seems to hate me.

G'day there One & All,

I'm trying to develop a sheet to assist with record keeping of hours worked & amounts paid. Basically my paychequeue records. I've got most of it working, but there is one part that has me stumped. I've tried cursing & swearing, and even stamped my feet but to no avail. I hope someone here can see where I've gone wrong, as even Mr Google has let me down on this occasion. I'm sure it something both simple & obvious but that I simply can't see. Here goes:

I have a number of columns formatted as a yearly calendar. I can't even claim credit, as I modified one I found online.
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"calendar.png","data-attachmentid":1203812}[/ATTACH]
On the same worksheet I have dates entered in "3/10/2017" format. I use =DAY(A3) to extract the day. First date entry is in A3. This works fine. I also use =MATCH(CHOOSE(MONTH(A3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),M2:AJ2,0)+12 to extract the search column from the Month labels across the top. This too works fine. This formula is in K5, the +12 at the end is an offset.

The next part is to use the formula "=MATCH(DAY(A3), ADDRESS(3,K5-1) & ":" & ADDRESS(49,K5-1),0)" to extract the correct row that matches the selected date. The 3 for the ADDRESS row is the top row of the calendar under the labels and 49 is the bottom ADDRESS row. "Week" rows are blank.

Parts of this final MATCH function must be working as it evaluates to "MATCH(3,$AE$3:$AE$49,0)" before the next step gives a #VALUE! error. The formula appears correct to here so I assume it's something to do with data types but I can't quite figure it.

Can anyone please point me in the right direction? Or see anything obvious for me to consider?
Thanks for reading,
Ken

Ok. Seems reasonable

Quote from AliGW

Try this:

=MATCH(DAY(A3), INDIRECT(ADDRESS(3,K5-1) & ":" & ADDRESS(49,K5-1)),0)

It will, of course, return #N/A as there is no match in that range.

What are you expecting it to do?

Display More

G'day there AliGW,

Thanks for taking the time

I expect it to return the row in which the number returned by "DAY(A3)" - which is 3 (Australian date format) - appears in the range AE3:AE49. That's the column containing the dates for the month of October, that column is returned from the formula in K5. When working the calculation step by step, it gives the correct addresses and the range does contain the number 3. However I then get the #VALUE error which indicates to me that there IS a match in that range, but it's the wrong data type. Or I've misunderstood the Excel help files - a quite likely possibility.

When & if I can get it to work correctly I intend to join them altogether into a single megaformula (a la Chip Pearson and/or John Walkenbach) and use it in the "Hours" column "I" to display total hours for the week. I have 2 options for that, but I'll choose later. I'm more concerned with getting it to work properly first.

Thanks for your suggestion,
Ken

I've attached a workbook showing, I think, what I'm expecting. It's only 3 cells that I've highlighted and added comments to. Everything is working fine except for the last calculation step of the MATCH formula now in L6. The =DAY(A3) formula returns '3' as expected, the =MATCH(CHOOSE(MONTH(A3)...) formula in L6 returns the column to the left of where it finds 'OCT' as required. There IS a '3' in that column at row 11, so everything is in place but it still returns a #VALUE! error in L6 and not '11' as I expected.

Thanks for your patience,
Ken

Thanks very much for this. I'll have a play with it over the next week or so and see what happens. If you hear a loud explosion then you'll know I've buggered it somehow

Thanks also for the note about the DAY(A3) bit. It's always obvious once someone explains it to me LOL.

I really do appreciate your taking the time to help,
Thanks again,
Ken

Nothing wrong with living there!! My wife was born in Leicester. I'd love to visit one day, but they want me to give them real live money in exchange for plane tickets. What sort of weird business transaction is that?

Haven't tried the formula as yet. Been working nights and trying to sleep/do "life stuff" during the day (shopping, paying bills, etc.) Got a few days off now so will have a go at it over the weekend

Almost got some sleep last night, and have walked dogs this morning. Your formula seems to be working fine, although I did confuse myself with the K5 reference until I realised that I'd deleted a column here and the address had changed

Even though your formula is great, and is working, I'm still getting errors but it's coming back to the MATCH function searching for an integer in a column of dates. Working step by step shows the error at that very last stage and checking the formatting provides that I'm searching through a column of date serial numbers, not integers - as you rightly pointed out. I think I may need to address that issue before I can get any further.

If the worst comes to the very worst, I can always implement VBA code and use copy/paste special to simply replace the formulae with integer results once they're calculated. I've done it previously but for a different problem in a different situation. It's not really what I want to do here but it is an option.

Thanks again for not abandoning me
Hope your weather is warming over there,
See ya,
Ken

G'day there Ali,

I think I may be on my way to solving it, although I've got to leave for work soon so I'll have to fiddle with it later. The MATCH function kept telling me there was a wrong data type which I assumed to be either the Lookup Value, or the data in the Lookup Array. However I had a look at the INDIRECT() function you had used and then read more about it on Chip Pearson's page http://www.cpearson.com/excel/indirect.htm which described my situation here. A quick check of what I had done then showed that the ADDRESS() functions I was using to determine the Lookup Array were returning a string, not a range. INDIRECT() fixed that in a sample MATCH() that I set up to check it. So the data type WAS wrong, just not in the data I was looking at. It was what the ADDRESS() function was returning that was the issue. I'll have to get stuck in over the weekend and see how I go but I think I've got it now.

Thanks again for your assistance, it was much appreciated. I'll post again if I work it out properly