Once again, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen show us multiple methods of achieving our intended result. Today, in Episode #1641 The Dueling Excel Podcast takes on finding the Address of Matching Cells. Carlos want to find the exact match to a Value in his Data Matrix and return the Address of the Cell which cntains that Value. Mike and Bill take different approaches to the question and the means to obtaining the result. Follow along to see how it is done!
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
This is Excel podcast Dueling Excel episode 112. Address of the matching cell.
All right, Carlos, from Portugal has this question.
He wants to find an exact match for a numerical value within the matrix and retrieve the cell reference at Al I think he means the cell address.
So I'm thinking about this here and I'm going to do what I always do.
Homeless switch over to VBA and see if I can write a user defined function that will I I'll pass it.
This number, I'll pass it, this array. And it'll tell me the address of the match. All right.
So in VBA, that was all F 11 to get the VBA insert module. I'll just call this function where is, and I'm going to pass it.
Look for comma.
Look in, those are gonna be the two arguments I'll say for each cell in look in. If a cell dot value is equal to look for then where is, is equal to cell dot address.
And at that point we can exit the loop. We don't have to keep checking anymore next cell.
And then what should we do?
If there's nothing found, we need to kind of initialize where is as equal to all right, let's give it a try here equal. Where is this value within this array? Press enter B 16 area.
Let's try another one. We'll put in 17. 11 is in F 20. There you go.
Mike, I'm gonna love to, I'm sure you're going to write some form that it's going to make my head spin.
So let's see it, Thanks MrExcel, Hey you're the one making my head spin that VBA.
It's making my head spin hand. It's so much easier than what I'm about to do. Look at that where it is.
All right, we'll come over here. Here's our matrix. Now there's two ways we could do this.
We could use the address function, which means if you're looking up 16 we need this number 61 here, we need a row 16 and second column.
If you're using the index function to look up a cell reference, we'd need the relative position, which would be four and two.
Let's look at just the condition entire matrix, anything in there equal to 61. That'll give us a bunch of trues and false. Only one. True.
There are no duplicates ever in this data set. We could simply multiply that times.
Hey, the row function. Now we can highlight this entire matrix.
And You might think that it would give us a bunch of row numbers, but it's only it's, it's going to be efficient.
If I highlight this in hand F nine, you can see, Oh, it gives me just a 13, not a bunch of 13.
So this is a different dimension than that array of trues and false. No problem.
When we multiply these two things, boom, we get a 16, the row number. All the rest are zeros.
Now I can simply, since I need that number, I could wrap it in some function. I'm going to put it in some product, by the way, we cannot.
We're we're putting this Boolean multiplying into the single argument here.
We can't split this apart here because there are different dimensions and some product requires that they're the same dimension. Now I'm going to copy control C.
The way we get around the different dimensions is by doing the multiplying. Right?
Now for column, I'm simply going to do the same thing, but here I'm going to put column and there, it will give me the column. Now I can come down here.
And actually, I'm going to copy both of these controls, CC to open up the clipboard escape, and then copy this control C. Now I come over here and do the address function.
Hey, where's the row number? Boom. There it is. Comma. Where's the column number, boom.
There it is the type of reference I want. I want to show it as a relative cell reference.
So I'm putting four, there's be 60.
Now, what if we wanted to actually add from this position all the way up to the first, then if we change this number right to six, six to nine, we'd add from the very top corner all the way down to that.
Well, we could simply take this address, which is text and convert it back to a reference using in direct function.
Indirect function takes texts that represents a reference and converts it back to a reference. And I'm going to say a 13.
I'm sitting in an eight, 13 up. I mean, that's the top corner. So a 13 colon, double coats in, join it.
Now, check this out. If I highlight all this indirect boom, it's getting from there all the way down to here.
When I change this input, this range will change a dynamic range.
Now I'm simply going to put some around this and guess what I can just hit enter that will not require control shift enter.
Now, what if we want to do this with index?
The one drawback to indirect is it's that it's volatile, right? If we could look up the cell reference then we could avoid that indirect.
The only problem is we have to take both of these things and instead of giving it just the row number, we have to say, Hey, take all those rows and then subtract the row.
And we'll take the very first one.
So 13 minus 13 would be a zero and we don't want that to start our array of a relative position. So we add one back in.
All right, so this will give us right here. Pollute F nine are one, two, three, four, five, six, seven, eight.
And so now when I, it gives me relative position four.
So we took with that little bit from just the row numbers to relative position. So we do the same thing now for the column.
And I'm going to double click that and type col tab, double click col tab. All right. So now we have those two things. Guess what?
Now we put them in these two things into the index and we can actually look up a cell reference.
Now, the way that works is you actually have to have the, well let's do the index.
All right, here's the whole array comma.
And then the row number that one, comma, the column number that now guess what right now index is going to do its thing.
It's going to look up that, which is totally useless, but recognize that it is doing a two-way lookup right row and column member within this larger matrix.
But as soon as we put the index function into the context, oops, it's 13 into the context of a cell reference now index that says, Oh no, I should be a cell reference cause of that colon.
So now when I highlight this and F nine, boom, there's that same dynamic range, we can simply put this into the saw. And so there we go.
Now let's try it. Six, six, two, nine, and boom.
We can even come over here and highlight from six, six to nine up and look down in our status bar here and sure enough, the Harris that number.
Absolutely amazing. All right. Throw it back to MrExcel. Mike. That is absolutely wild. Okay.
So we have the index function, the index of the matrix.
I get that this sum product figures out the row, the relative wrote this on product figures out the column using the Boolean modification.
Then at the very end, you throw in this obscure fact that you can do some of eight, 13 to the colon of that whole big formula. And the thing works. That is incredible. Absolutely amazing.
You know, so for those of you have been watching dueling podcasts for a long time. Yeah.
I would always go to VBA and Mike would always go to a formula that was the traditional way.
But since Mike started working on control shift enter you will have noticed that his formula answers have just gone through the roof.
Absolutely amazing. I can't wait for this book to arrive.
I want to thank everyone for Bye this week. We'll see you next week for another dueling Excel podcast from MrExcel and Excel is fun.
All right, Carlos, from Portugal has this question.
He wants to find an exact match for a numerical value within the matrix and retrieve the cell reference at Al I think he means the cell address.
So I'm thinking about this here and I'm going to do what I always do.
Homeless switch over to VBA and see if I can write a user defined function that will I I'll pass it.
This number, I'll pass it, this array. And it'll tell me the address of the match. All right.
So in VBA, that was all F 11 to get the VBA insert module. I'll just call this function where is, and I'm going to pass it.
Look for comma.
Look in, those are gonna be the two arguments I'll say for each cell in look in. If a cell dot value is equal to look for then where is, is equal to cell dot address.
And at that point we can exit the loop. We don't have to keep checking anymore next cell.
And then what should we do?
If there's nothing found, we need to kind of initialize where is as equal to all right, let's give it a try here equal. Where is this value within this array? Press enter B 16 area.
Let's try another one. We'll put in 17. 11 is in F 20. There you go.
Mike, I'm gonna love to, I'm sure you're going to write some form that it's going to make my head spin.
So let's see it, Thanks MrExcel, Hey you're the one making my head spin that VBA.
It's making my head spin hand. It's so much easier than what I'm about to do. Look at that where it is.
All right, we'll come over here. Here's our matrix. Now there's two ways we could do this.
We could use the address function, which means if you're looking up 16 we need this number 61 here, we need a row 16 and second column.
If you're using the index function to look up a cell reference, we'd need the relative position, which would be four and two.
Let's look at just the condition entire matrix, anything in there equal to 61. That'll give us a bunch of trues and false. Only one. True.
There are no duplicates ever in this data set. We could simply multiply that times.
Hey, the row function. Now we can highlight this entire matrix.
And You might think that it would give us a bunch of row numbers, but it's only it's, it's going to be efficient.
If I highlight this in hand F nine, you can see, Oh, it gives me just a 13, not a bunch of 13.
So this is a different dimension than that array of trues and false. No problem.
When we multiply these two things, boom, we get a 16, the row number. All the rest are zeros.
Now I can simply, since I need that number, I could wrap it in some function. I'm going to put it in some product, by the way, we cannot.
We're we're putting this Boolean multiplying into the single argument here.
We can't split this apart here because there are different dimensions and some product requires that they're the same dimension. Now I'm going to copy control C.
The way we get around the different dimensions is by doing the multiplying. Right?
Now for column, I'm simply going to do the same thing, but here I'm going to put column and there, it will give me the column. Now I can come down here.
And actually, I'm going to copy both of these controls, CC to open up the clipboard escape, and then copy this control C. Now I come over here and do the address function.
Hey, where's the row number? Boom. There it is. Comma. Where's the column number, boom.
There it is the type of reference I want. I want to show it as a relative cell reference.
So I'm putting four, there's be 60.
Now, what if we wanted to actually add from this position all the way up to the first, then if we change this number right to six, six to nine, we'd add from the very top corner all the way down to that.
Well, we could simply take this address, which is text and convert it back to a reference using in direct function.
Indirect function takes texts that represents a reference and converts it back to a reference. And I'm going to say a 13.
I'm sitting in an eight, 13 up. I mean, that's the top corner. So a 13 colon, double coats in, join it.
Now, check this out. If I highlight all this indirect boom, it's getting from there all the way down to here.
When I change this input, this range will change a dynamic range.
Now I'm simply going to put some around this and guess what I can just hit enter that will not require control shift enter.
Now, what if we want to do this with index?
The one drawback to indirect is it's that it's volatile, right? If we could look up the cell reference then we could avoid that indirect.
The only problem is we have to take both of these things and instead of giving it just the row number, we have to say, Hey, take all those rows and then subtract the row.
And we'll take the very first one.
So 13 minus 13 would be a zero and we don't want that to start our array of a relative position. So we add one back in.
All right, so this will give us right here. Pollute F nine are one, two, three, four, five, six, seven, eight.
And so now when I, it gives me relative position four.
So we took with that little bit from just the row numbers to relative position. So we do the same thing now for the column.
And I'm going to double click that and type col tab, double click col tab. All right. So now we have those two things. Guess what?
Now we put them in these two things into the index and we can actually look up a cell reference.
Now, the way that works is you actually have to have the, well let's do the index.
All right, here's the whole array comma.
And then the row number that one, comma, the column number that now guess what right now index is going to do its thing.
It's going to look up that, which is totally useless, but recognize that it is doing a two-way lookup right row and column member within this larger matrix.
But as soon as we put the index function into the context, oops, it's 13 into the context of a cell reference now index that says, Oh no, I should be a cell reference cause of that colon.
So now when I highlight this and F nine, boom, there's that same dynamic range, we can simply put this into the saw. And so there we go.
Now let's try it. Six, six, two, nine, and boom.
We can even come over here and highlight from six, six to nine up and look down in our status bar here and sure enough, the Harris that number.
Absolutely amazing. All right. Throw it back to MrExcel. Mike. That is absolutely wild. Okay.
So we have the index function, the index of the matrix.
I get that this sum product figures out the row, the relative wrote this on product figures out the column using the Boolean modification.
Then at the very end, you throw in this obscure fact that you can do some of eight, 13 to the colon of that whole big formula. And the thing works. That is incredible. Absolutely amazing.
You know, so for those of you have been watching dueling podcasts for a long time. Yeah.
I would always go to VBA and Mike would always go to a formula that was the traditional way.
But since Mike started working on control shift enter you will have noticed that his formula answers have just gone through the roof.
Absolutely amazing. I can't wait for this book to arrive.
I want to thank everyone for Bye this week. We'll see you next week for another dueling Excel podcast from MrExcel and Excel is fun.
