You have two lists of data in Excel. If something from List2 is found in List1, put the address of the matching cell next to the item in List2. Mike and Bill try to find a one-cell formula using either CELL or ADDRESS in this week's episode.
Table of Contents
(0:00) Description of problem
(0:30) Does ADDRESS or CELL return a spillable array?
(2:02) Can XLOOKUP return an array of references?
(3:33) Bill's answer: a single ADDRESS formula that spills
(5:47) Mike's first answer: using old-school CELL function and copying
(8:07) Mike's second answer: using LET and XLOOKUP
(10:05) Wrap-up
Table of Contents
(0:00) Description of problem
(0:30) Does ADDRESS or CELL return a spillable array?
(2:02) Can XLOOKUP return an array of references?
(3:33) Bill's answer: a single ADDRESS formula that spills
(5:47) Mike's first answer: using old-school CELL function and copying
(8:07) Mike's second answer: using LET and XLOOKUP
(10:05) Wrap-up
Transcript of the video:
Hey, welcome back. It is time for another Dueling Excel podcast.
I am Bill Jelen from MrExcel. We'll be joined by Mike Girvin from Excel is Fun.
This is our episode 194. The cell address of matching values.
Today's question from DW.
I want to get the cell reference when an item in List02 is in List01.
So right here when we are looking at this 59, it is actually in cell B10.
DW is looking for us to tell him that it's in B10.
But you see a lot of them are not found.
My thought process on this is there are two easy ways to get a cell address.
Either the ADDRESS function or the CELL function with the "address" argument. Let's take a look at those. There's ADDRESS.
This is a function that I hardly ever use.
I am not sure what I am supposed to put in there.
So a great trick that popped up from MVP Nabil Mourad and also from Deborah' Dalgleish in the last two weeks.
Don't press Ctrl+A but press Ctrl+Shift+A.
It will insert the names so we can figure it out.
So if I would just, for example, put in here H7 for the row number. Then column number 1.
Then for Absolute, do we want dollar signs? Or dollar signs just on the row?
Or dollar signs just on the column? Or relative? I will choose 4 for Relative. Then do we want A1 style?
Of course we do.
So that's a one.
For Sheet Text, we don't need to prefix it with a sheet name. That successfully returns A1.
But then, the big question is what would happen if we instead of H7 said H7#? So in other words that whole array of SEQUENCE.
ADDRESS works great with an array.
It returns all of those values.
Another way to do this is using the CELL function.
In the first argument we say we want the ADDRESS and then H7.
But the question is when we put in H7# will it return the cell addresses from all of those items?
And the answer is no. So ADDRESS can work with arrays.
And CELL cannot work with arrays.
I am just going to take a side journey here.
When I initially started thinking about this I thought, "oh this is going to be one of those great uses for when we can use XLOOKUP with a colon next to it. XLOOKUP, if we just did XLOOKUP of D9 in LIST01.
And returning List01.
We will get the 59 that we were looking for. Do there's the the D9 and it gives us the 59 there.
But if we would put the XLOOKUP():XLOOKUP() then amazingly that formula doesn't return 59.
It returns B10. We can see this if we go into Evaluate Formula.
I will click Evaluate, Evaluate, Evaluate.
Right here is about where it's about to do the XLOOKUP.
Instead of giving us the 59 it returns B10. And that all happens because of this colon right here.
Okay, so we know that in XLOOKUP we can look up all of our values at once.
So look up everything in D14:D15.
But when I try and combine this trick and this trick. By saying I want to look up two values.
Then colon, Look up the same two values. And then pass that to the CELL("address".
It does not work. Here is why? If we come into Evaluate Formula.
And get to the point where it's about to Evaluate that first XLOOKUP.
Right here, this is where it should say B13, comma B12.
Instead it says 88 and 10.
That shot down my first way to go.
So here is the actual solution For me, it was three steps.
To get this, the first thing I want to do is use XMATCH.
It is the new function to locate where the item is in List01. So a simple little function.
Look up all of these values here in list01.
And that's all.
We don't need to say it's an exact match because it's automatic.
So the first one: seven is the second item in the list. 54 is not found.
59 is the fourth item in the list. 18 is not found. So that's good.
now we know where the things are. Okay then the ADDRESS function.
The ADDRESS function says Which row and Which column?
I know that it's always going to be in column two.
So for the column part, that was actually easy.
I can just ask for the COLUMN of List01 and that will return column 2.
To get the location as far as which row, though. I started with the ROW() of List01.
Because this is a dynamic array, it gives me all of the rows.
So I had to say I want the INDEX of that. Comma one. Which successfully gave me just 5.
I subtracted 1 to get up to the cell above that.
And then added in the results of the XMATCH.
Finally out here this 4 says that we want a relative reference.
and that one formula returns all of these answers so B6, B8, B6, B11, B10. If we would change something here we'll take this 32 and make it be 12.
Then that changes to B9.
It is working except for the #N/A errors.
So finally just wrap that whole thing in IFNA and display "".
So a single formula using ADDRESS to return all of those answers.
Mike, let's see what you have. [ Mike Girvin ] Wow, that spilling formula is amazing, MrExcel!
You get that little bit to look up row five.
Then you subtract one to jump back to row four. And then subtract the array generated by XMATCH.
All to create the correct spilled row numbers for ADDRESS.
So I guess I better do an old school formula. Instead of ADDRESS. I will use the CELL function.
Now, anytime you are comparing two lists, the MATCH function is the way to go.
We are looking that up within this list right here.
That's a defined name so it's automatically locked.
And we are looking for exact match.
So unlike XMATCH, we have to put the third argument and say do exact match.
That will tell us the relative position when one of the items is in the list.
There is the fourth position. Or #N/A.
I am going to put that inside of INDEX because I really want to look up the cell reference.
So we are looking through there. Comma. And there is the row number.
Now of course if you enter it into the cell, it returns the actual value.
But press F2 to edit.
If you put INDEX function in the context of a cell reference by using the cell function.
And we ask for the "address" in the first argument, then INDEX will know to deliver the cell address. Close parentheses.
And to populate it through the cells that are highlighted, I use Control+Enter. There we have our cell reference.
Ohhhh, but this is not like the ADDRESS that gives us that cool argument where we can put a 4 to get relative cell reference.
It is always going to deliver with dollar signs. So F2 to edit.
Now we use SUBSTITUTE. That's the text. Comma.
The old text we're searching for is a dollar sign.
And we'll replace it with double quote double quote. Which means nothing. Close parentheses.
Control+Enter to populate it all the way down.
Now, I think IFNA came in 2013 or 2016. So, if you are in earlier versions.
We are going to have to run the IF formula.
Now that's the little bit that compares the two lists.
So I will copy that and right after the equal sign, I will say IF and then we'll use ISNA and we'll paste the match. Close parentheses. There's the logical test.
If it's TRUE, then i want to show double quote double quote.
Now technically that's a zero length text string.
Which will show nothing in the cell. Otherwise it will run the formula.
Close parentheses. Control+Enter. Double-click and send it down.
So if you're in an older version, that formula will work.
Now let's see if we can get the LET function.
This is Microsoft 365. So the latest version.
And before i use LET, I am going to use XLOOKUP to try and look up a reference.
There's our lookup value. Comma. The lookup array.
Comma. And with XLOOKUP, I have to list it twice.
Since there's only one column and if I copy that down of course it delivers the value.
But now, if i put it inside of ROW, this is a context where a cell reference is expected.
And so like INDEX, the XLOOKUP is delivering the reference.
And ROW can see it and deliver the row number. And for 59 that's exactly what we need.
Press F2.
Now I did that just to see if it XLOOKUP would work. Now let's do LET.
The name of the variable we're going to use is Get. And then that will be XLOOKUP.
And the advantage to using LET is of course we're going to use this a couple of times.
LET defines it as a variable and calculates it only once. Comma.
Our formula will be IFNA(ADDRESS and then in the first argument it needs the row. And that's the Get variable. Comma.
And for column we'll use COLUMN and the Get variable.
Comma and there's that beautiful 4 for relative cell reference.
Comma. Now for Value_If_NA we will show nothing. Double quote double quote.
Close parentheses. And there's our formula.
We have one variable. XLOOKUP. And our formula.
It has that single input and so it's going to deliver a single output.
But since I have the whole column highlighted already I can populate this with Ctrl+Enter. So super old school and super new school.
Except for it's not spilling. All right, I will throw it back to MrExcel.
Well isn't that cool? Two different ways, Mike.
One using CELL and one using ADDRESS. Both have to be copied down.
But the thing that i learned there that I used to know and I forgot.
In addition to having the colon next to XLOOKUP to coerce it into a reference, INDEX, ROW, and COLUMN all are tools for getting that reference out of XLOOKUP.
Very very cool. Well, hey I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelisFun.
I am Bill Jelen from MrExcel. We'll be joined by Mike Girvin from Excel is Fun.
This is our episode 194. The cell address of matching values.
Today's question from DW.
I want to get the cell reference when an item in List02 is in List01.
So right here when we are looking at this 59, it is actually in cell B10.
DW is looking for us to tell him that it's in B10.
But you see a lot of them are not found.
My thought process on this is there are two easy ways to get a cell address.
Either the ADDRESS function or the CELL function with the "address" argument. Let's take a look at those. There's ADDRESS.
This is a function that I hardly ever use.
I am not sure what I am supposed to put in there.
So a great trick that popped up from MVP Nabil Mourad and also from Deborah' Dalgleish in the last two weeks.
Don't press Ctrl+A but press Ctrl+Shift+A.
It will insert the names so we can figure it out.
So if I would just, for example, put in here H7 for the row number. Then column number 1.
Then for Absolute, do we want dollar signs? Or dollar signs just on the row?
Or dollar signs just on the column? Or relative? I will choose 4 for Relative. Then do we want A1 style?
Of course we do.
So that's a one.
For Sheet Text, we don't need to prefix it with a sheet name. That successfully returns A1.
But then, the big question is what would happen if we instead of H7 said H7#? So in other words that whole array of SEQUENCE.
ADDRESS works great with an array.
It returns all of those values.
Another way to do this is using the CELL function.
In the first argument we say we want the ADDRESS and then H7.
But the question is when we put in H7# will it return the cell addresses from all of those items?
And the answer is no. So ADDRESS can work with arrays.
And CELL cannot work with arrays.
I am just going to take a side journey here.
When I initially started thinking about this I thought, "oh this is going to be one of those great uses for when we can use XLOOKUP with a colon next to it. XLOOKUP, if we just did XLOOKUP of D9 in LIST01.
And returning List01.
We will get the 59 that we were looking for. Do there's the the D9 and it gives us the 59 there.
But if we would put the XLOOKUP():XLOOKUP() then amazingly that formula doesn't return 59.
It returns B10. We can see this if we go into Evaluate Formula.
I will click Evaluate, Evaluate, Evaluate.
Right here is about where it's about to do the XLOOKUP.
Instead of giving us the 59 it returns B10. And that all happens because of this colon right here.
Okay, so we know that in XLOOKUP we can look up all of our values at once.
So look up everything in D14:D15.
But when I try and combine this trick and this trick. By saying I want to look up two values.
Then colon, Look up the same two values. And then pass that to the CELL("address".
It does not work. Here is why? If we come into Evaluate Formula.
And get to the point where it's about to Evaluate that first XLOOKUP.
Right here, this is where it should say B13, comma B12.
Instead it says 88 and 10.
That shot down my first way to go.
So here is the actual solution For me, it was three steps.
To get this, the first thing I want to do is use XMATCH.
It is the new function to locate where the item is in List01. So a simple little function.
Look up all of these values here in list01.
And that's all.
We don't need to say it's an exact match because it's automatic.
So the first one: seven is the second item in the list. 54 is not found.
59 is the fourth item in the list. 18 is not found. So that's good.
now we know where the things are. Okay then the ADDRESS function.
The ADDRESS function says Which row and Which column?
I know that it's always going to be in column two.
So for the column part, that was actually easy.
I can just ask for the COLUMN of List01 and that will return column 2.
To get the location as far as which row, though. I started with the ROW() of List01.
Because this is a dynamic array, it gives me all of the rows.
So I had to say I want the INDEX of that. Comma one. Which successfully gave me just 5.
I subtracted 1 to get up to the cell above that.
And then added in the results of the XMATCH.
Finally out here this 4 says that we want a relative reference.
and that one formula returns all of these answers so B6, B8, B6, B11, B10. If we would change something here we'll take this 32 and make it be 12.
Then that changes to B9.
It is working except for the #N/A errors.
So finally just wrap that whole thing in IFNA and display "".
So a single formula using ADDRESS to return all of those answers.
Mike, let's see what you have. [ Mike Girvin ] Wow, that spilling formula is amazing, MrExcel!
You get that little bit to look up row five.
Then you subtract one to jump back to row four. And then subtract the array generated by XMATCH.
All to create the correct spilled row numbers for ADDRESS.
So I guess I better do an old school formula. Instead of ADDRESS. I will use the CELL function.
Now, anytime you are comparing two lists, the MATCH function is the way to go.
We are looking that up within this list right here.
That's a defined name so it's automatically locked.
And we are looking for exact match.
So unlike XMATCH, we have to put the third argument and say do exact match.
That will tell us the relative position when one of the items is in the list.
There is the fourth position. Or #N/A.
I am going to put that inside of INDEX because I really want to look up the cell reference.
So we are looking through there. Comma. And there is the row number.
Now of course if you enter it into the cell, it returns the actual value.
But press F2 to edit.
If you put INDEX function in the context of a cell reference by using the cell function.
And we ask for the "address" in the first argument, then INDEX will know to deliver the cell address. Close parentheses.
And to populate it through the cells that are highlighted, I use Control+Enter. There we have our cell reference.
Ohhhh, but this is not like the ADDRESS that gives us that cool argument where we can put a 4 to get relative cell reference.
It is always going to deliver with dollar signs. So F2 to edit.
Now we use SUBSTITUTE. That's the text. Comma.
The old text we're searching for is a dollar sign.
And we'll replace it with double quote double quote. Which means nothing. Close parentheses.
Control+Enter to populate it all the way down.
Now, I think IFNA came in 2013 or 2016. So, if you are in earlier versions.
We are going to have to run the IF formula.
Now that's the little bit that compares the two lists.
So I will copy that and right after the equal sign, I will say IF and then we'll use ISNA and we'll paste the match. Close parentheses. There's the logical test.
If it's TRUE, then i want to show double quote double quote.
Now technically that's a zero length text string.
Which will show nothing in the cell. Otherwise it will run the formula.
Close parentheses. Control+Enter. Double-click and send it down.
So if you're in an older version, that formula will work.
Now let's see if we can get the LET function.
This is Microsoft 365. So the latest version.
And before i use LET, I am going to use XLOOKUP to try and look up a reference.
There's our lookup value. Comma. The lookup array.
Comma. And with XLOOKUP, I have to list it twice.
Since there's only one column and if I copy that down of course it delivers the value.
But now, if i put it inside of ROW, this is a context where a cell reference is expected.
And so like INDEX, the XLOOKUP is delivering the reference.
And ROW can see it and deliver the row number. And for 59 that's exactly what we need.
Press F2.
Now I did that just to see if it XLOOKUP would work. Now let's do LET.
The name of the variable we're going to use is Get. And then that will be XLOOKUP.
And the advantage to using LET is of course we're going to use this a couple of times.
LET defines it as a variable and calculates it only once. Comma.
Our formula will be IFNA(ADDRESS and then in the first argument it needs the row. And that's the Get variable. Comma.
And for column we'll use COLUMN and the Get variable.
Comma and there's that beautiful 4 for relative cell reference.
Comma. Now for Value_If_NA we will show nothing. Double quote double quote.
Close parentheses. And there's our formula.
We have one variable. XLOOKUP. And our formula.
It has that single input and so it's going to deliver a single output.
But since I have the whole column highlighted already I can populate this with Ctrl+Enter. So super old school and super new school.
Except for it's not spilling. All right, I will throw it back to MrExcel.
Well isn't that cool? Two different ways, Mike.
One using CELL and one using ADDRESS. Both have to be copied down.
But the thing that i learned there that I used to know and I forgot.
In addition to having the colon next to XLOOKUP to coerce it into a reference, INDEX, ROW, and COLUMN all are tools for getting that reference out of XLOOKUP.
Very very cool. Well, hey I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelisFun.
