# 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?
Ken

## Images

• Attach the workbook, not a picture of it. Nobody is going to recreate your data in order to help you.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Ok. Seems reasonable

## Files

• Try this:

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

What are you expecting it to do?

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

• Provide a workbook that shows (manually) what you are trying to do. That is, that contains manually entered expected outcomes.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

## Files

• This will return the value (if one exists) immediately to the right of the day number in the relevant month's column:

I do not have time to explain it to you now (I am just about to go out to work), but if you are unable to back engineer it yourself, then post again and I'll take a look later. Be aware that the DAY(A3) idea is flawed, because what is in the calendar matrix is not really 3 (it just looks like a 3 because of the cell formatting). It's actually the full date 03/10/17.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

• No worries, Ken - just post again to this thread if you need further guidance and I'll see what I can do. We have a time lag, of course, as I'm in Blighty!

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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?

• Nothing wrong with living here at all! I grew up not far from Leicester, just over the border in Derbyshire (on the Derbyshire - Nottinghamshire - Leicestershire border, next to the River Trent). I'm down in the south-east now (Suffolk).

How did you get on with the formula?

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

• I had wondered why I'd heard nothing ... Let me know how you get on.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

• Glad it’s working - at least partially.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Hi, I want to compare both Column A and Column B address are both the address match. What is the best formula to go for it? Tq.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

• Glad to hear it, Ken! I'm on holiday from school now (in the hot and very sunny UK!) for the next few weeks, so I should be able to help with any issues fairly promptly.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!