I want to revisit a topic from podcast 613; using the COLUMN() function in place of putting a 2 as the third argument in a VLOOKUP function. Several questions came in about that technique, and in Episode 629, I will try to clear up the confusion.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netCast. I'm Bill Jelen.
Hey, If you have a question for the netcast please feel free to send it in.
Either leave a voicemail or drop me a note bill@MrExcel.com.
Now a couple of weeks ago, we talked about using the column function and I got several questions about that where I just guess I didn't make it clear exactly what was going on there.
When we have a VLOOKUP.
I have a VLOOKUP here.
That's hard-coded to say for the third primer that we want the second column of the table.
And then I copy that across.
Ofcourse it's going to give me the second column all the way across because that too, is basically hard-coded and what I was trying to do with the column function.
And I don't think was clear during the podcast, is I want to replace that 2 with a function that's going to return a 2.
In the column function, let's just come over here =COLUMN(A1) will give me a 1.
=COLUMN(B1) will give me a 2 of course.
Column no B1 gives me 2 because it's the second column.
So it doesn't matter where my data is.
In this case, here I want to grab data from column I and column J and column K.
That's completely irrelevant.
What is relevant is that it's the 2nd, 3rd and 4th columns of my table.
So over here where I have that 2 hard coded, I want to ask for the column, and I'm gonna ask for B1.
I could easily ask for B9 B9000 B50000.
It doesn't really matter, I'm just choosing some value in column B.
And the advantage there is, it gives me the exact same value because column no B1, it's just like writing 2.
But when I copy that across, it gives me the third fourth and fifth columns from the table.
So there's, you know it was just coincidental during the original podcast that we ended up asking for the column no B1 and the data was in B1 that had nothing to do with it.
It just happened to be that the data was in the second column.
So cool little function.
Someone else wrote in they said. You know hey you completely missed it, why don't you just put some numbers up here 2 3 4 & 5 and point to those numbers in the function and absolutely that would work as well, and I've done that trick before so we would say that we want to always point a O$1 and that way it will point as well.
Depends on whether you want to add the extra text in or use this column function.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Hey, If you have a question for the netcast please feel free to send it in.
Either leave a voicemail or drop me a note bill@MrExcel.com.
Now a couple of weeks ago, we talked about using the column function and I got several questions about that where I just guess I didn't make it clear exactly what was going on there.
When we have a VLOOKUP.
I have a VLOOKUP here.
That's hard-coded to say for the third primer that we want the second column of the table.
And then I copy that across.
Ofcourse it's going to give me the second column all the way across because that too, is basically hard-coded and what I was trying to do with the column function.
And I don't think was clear during the podcast, is I want to replace that 2 with a function that's going to return a 2.
In the column function, let's just come over here =COLUMN(A1) will give me a 1.
=COLUMN(B1) will give me a 2 of course.
Column no B1 gives me 2 because it's the second column.
So it doesn't matter where my data is.
In this case, here I want to grab data from column I and column J and column K.
That's completely irrelevant.
What is relevant is that it's the 2nd, 3rd and 4th columns of my table.
So over here where I have that 2 hard coded, I want to ask for the column, and I'm gonna ask for B1.
I could easily ask for B9 B9000 B50000.
It doesn't really matter, I'm just choosing some value in column B.
And the advantage there is, it gives me the exact same value because column no B1, it's just like writing 2.
But when I copy that across, it gives me the third fourth and fifth columns from the table.
So there's, you know it was just coincidental during the original podcast that we ended up asking for the column no B1 and the data was in B1 that had nothing to do with it.
It just happened to be that the data was in the second column.
So cool little function.
Someone else wrote in they said. You know hey you completely missed it, why don't you just put some numbers up here 2 3 4 & 5 and point to those numbers in the function and absolutely that would work as well, and I've done that trick before so we would say that we want to always point a O$1 and that way it will point as well.
Depends on whether you want to add the extra text in or use this column function.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
