A simple MATCH formula is not working. In episode 1162, we wander through the Validation dialog, the Name Manager dialog, all trying to figure out why the MATCH formula is returning #N/A.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, today's question, frankly, it's sent in by me.
I'm trying to answer Shaun's question which will probably have to do tomorrow, but I can't figure this out.
Shaun has a drop-down up there, and he wants to figure out, when that drop-down changes he wants to bring a section of a report over.
So I knew that I had to go figure out, when he chooses January, where does that section of the report start?
Alright, and that's a =MATCH.
So I go look for this value, and over here we'll start in a well- Actually, you know what?
Let's just do the whole column, that should work, ,0 , because it's an exact match, and I get #N/A. What's up with that?
So I say “Alright well, let's see if this January in cell B3 is equal to this one over here, it's not!” Alright, so it's spelled wrong somewhere, J-A-N-U-A-R-Y, J-A-N-U-A-R-Y, wild.
Alright, so we have to come here, I'm going to go to Data and then Validation, Data Validation, and I see that they're pulling this from somewhere called MONTHS.
I won't figure out where that MONTHS is actually stored, so we come to Formula Name Manager, click on MONTHS, and I see that it is stored on the YEAR sheet, BE6:BE17, let's go out there.
So we come out here, BE6:BE17, Alright, and here I can finally, let's make this a little bit wider, so we can see what's going on, I press the F2 key.
Ah, look at that, there's a trailing space at the end.
Alright, so Shaun, very clever, setting up this spreadsheet with the data validation out here where no one can see it.
But unfortunately those spaces, unless you enter the exact same spaces in the other sheet, and apparently you did not, is going to cause a lot of problems.
Alright, so we have, you know, a relatively hard thing that we have to do for you tomorrow, but because of those spaces, we are having just initial problems.
So let's come here, and will again, choose from the list, now that the list does not have spaces, I'm getting the 3, beautiful.
and then February, the 31, and that's telling me what row, relative row, is found.
We’re going to use that information tomorrow to try and pull the rest of your report over.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, today's question, frankly, it's sent in by me.
I'm trying to answer Shaun's question which will probably have to do tomorrow, but I can't figure this out.
Shaun has a drop-down up there, and he wants to figure out, when that drop-down changes he wants to bring a section of a report over.
So I knew that I had to go figure out, when he chooses January, where does that section of the report start?
Alright, and that's a =MATCH.
So I go look for this value, and over here we'll start in a well- Actually, you know what?
Let's just do the whole column, that should work, ,0 , because it's an exact match, and I get #N/A. What's up with that?
So I say “Alright well, let's see if this January in cell B3 is equal to this one over here, it's not!” Alright, so it's spelled wrong somewhere, J-A-N-U-A-R-Y, J-A-N-U-A-R-Y, wild.
Alright, so we have to come here, I'm going to go to Data and then Validation, Data Validation, and I see that they're pulling this from somewhere called MONTHS.
I won't figure out where that MONTHS is actually stored, so we come to Formula Name Manager, click on MONTHS, and I see that it is stored on the YEAR sheet, BE6:BE17, let's go out there.
So we come out here, BE6:BE17, Alright, and here I can finally, let's make this a little bit wider, so we can see what's going on, I press the F2 key.
Ah, look at that, there's a trailing space at the end.
Alright, so Shaun, very clever, setting up this spreadsheet with the data validation out here where no one can see it.
But unfortunately those spaces, unless you enter the exact same spaces in the other sheet, and apparently you did not, is going to cause a lot of problems.
Alright, so we have, you know, a relatively hard thing that we have to do for you tomorrow, but because of those spaces, we are having just initial problems.
So let's come here, and will again, choose from the list, now that the list does not have spaces, I'm getting the 3, beautiful.
and then February, the 31, and that's telling me what row, relative row, is found.
We’re going to use that information tomorrow to try and pull the rest of your report over.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
