Going back to "VLOOKUP WEEK 2012" VLOOKUP WEEK Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen look at the 'LOOKUP' Function. Taking cues from an email Bill received from Dan Bricklin [co-founder/inventor of VisiCalc] Bill argues the point that "=LOOKUP" is ambiguous in Microsoft Excel when the Data Set Table is 'square'. Mike, on the other hand, thinks that "=LOOKUP" is not an ambiguous Function; that it is intuitive and dynamic. Follow along with Episode #1550 to hear the debate and decide for yourself.
Dueling Excel Podcast #106...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 #106...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:
Bill: Hey, welcome back to another Dueling Excel podcast.
I’m Bill Jelen from MrExcel.
We’re joined by Mike Griffin from ExcelisFun.
This is episode 106: =LOOKUP vs. @lookup.
Bill: Hey, this is a remnant from VLOOKUP week.
If you remember, I showed VisiCalc.
And that actually prompted an email from Dan Bricklin.
Dan Bricklin, the inventor, or co-inventor of VisiCalc.
And I then, copy Mike Girvin in on my reply and in that reply, I complained because I always complain that =LOOKUP and Excel version of LOOKUP is ambiguous.
And here’s what I’m talking about.
So, we have California.
I need to look up California in this list over here.
So, we do =LOOKUP California within this list, =LOOKUP(A3,F3:G9).
I’m using the array version and that’s it.
I don’t get to specify “,2,FALSE” because LOOKUP is always the TRUE version and lookup always returns the last column in the range.
Well, always, I don’t even get to specify V to specify that the table is vertical.
Excel just figures that out from the shape of the table and that means that LOOKUP can also do the equivalent of HLOOKUP, at least the TRUE version of HLOOKUP.
So, we can LOOKUP that table and that just decides on the shape of the table, whether it’s horizontal or vertical.
Well, that’s kind of a cool trick.
But, what happens when the table is square?
That’s when it’s ambiguous.
Mike: Thanks, Mr. Excel.
Ambiguous, it-- may be somewhat ambiguous.
But, I love the LOOKUP function.
Right here, this is great.
So, it’s taller than it’s wide.
So, we know it’s going to do vertical LOOKUP and I have to just center the LOOKUP value and the table is going to take it from the last column.
Now, the most important thing about LOOKUP is it only does the approximate LOOKUP or TRUE version.
So, right, this has to be sorted.
This one’s great.
Hey, it is sorted.
We know that if you can sort and use approximate match, it’s faster calculating too.
And this is great, you know.
We’re looking this up horizontal.
It’s much wider than it is tall, but it does know what to do with the square.
Anytime, it’s a square table like this, it’ll do VLOOKUP.
So, taller than wide or square does VLOOKUP.
Wider than tall, it does each LOOKUP.
So, =LOOKUP, and a lot of this, I have to just simply select that value in the table, no comma, column number or whatever.
ENTER, =LOOKUP(A13,D13:H17).
Now, of course, this has to be sorted.
Alright, so, back over to MrExcel.
Bill: Okay, alright.
Mike, I hear you.
When it’s square, it’s not ambiguous.
It treats it like a VLOOKUP.
But, here’s my problem.
The one thing that I really like about the LOOKUP function is it returns the last column.
Let’s say-- alright, so, let me edit this formula.
I’m going to make sure this is always looking up $A3.
We’re always reaching back to get the state in column A. And then, I only want to freeze this first A. So, it always starts looking in column A. But then, the range will automatically expand as I go across.
Alright.
Let’s just try this.
I’m going to make a go out of here, see if we can get all the values in yellow.
If I choose a different state, now, Ohio is pulling all of those values from Ohio.
And I feel I really good about this, right.
But then, as the month-- the months go on, and I continue to get more data-- let me just paste some more data here.
And this concept continues to expand right now A through G is 7 columns by 7 rows tall.
Now, all of a sudden, it stops working because at this point, I now have 8 columns and 7 rows.
So, it changes on the fly to an HLOOKUP instead of a VLOOKUP.
That’s what I mean by it’s ambiguous.
Mike: What?
That is amazing.
You’re going to kidding me.
Ambiguous, I can think of a lot of worse words and ambiguous describe that.
So, LOOKUP, when you have an expandable range once you get past the H, it just flat-out switches over.
Now, I’ve never seen that before.
I guess what it means is LOOKUP has lots of great uses.
But, don’t use it with an expandable range because yeah, it’ll switch right over.
You know, we can use the LOOKUP function to do this.
We’ll just use not the array version.
Not that we’re going to use this ARRAY, we use the lookup vector and the result vector, right.
So, the lookup vector, boom, F4 to lock that, comma, and the result vector.
We’ll leave it as a relative cell reference, =LOOKUP($A16,$A$6:$A$12,B6:B12).
And then, copy it over.
I love it.
Look at that, all the way across that little purple range is moving but yeah.
It’s ambiguous and lots of other words.
Alright.
I’m going to throw back over to MrExcel.
Bill: Oh, but, Mike, now, I have newfound respect for VisiCalc which invented LOOKUP.
I mean actually back in the days of VisiCalc, we literally only had 20 functions.
So, here’s the VisiCalc reference card with some MIN, MAX, COUNT, AVERAGE, NPV (net present value), and then, LOOKUP, NA, ERROR, PI, ABS (absolute value), INT (integer), and then a whole bunch of math things.
The EXP, SQRT, LN, LOG10, SIN, ASIN, COS, ACOS, TAN, and ATAN are the only functions, only 20 functions.
So, LOOKUP was one of the original functions and Dan Bricklin mentioned that Bob Franksen wanted LOOKUP in there so that we can do his 1978 tax return.
So, here I have the 1970 tax rate schedules.
Remarkably, all this stuff is online, and I set up a VisiCalc spreadsheet that would let me do this.
Now, there were three elements.
It was, you owed, a certain tax plus a percentage of the amount over the previous rate.
Okay, so, I’ve set up three different tables here with the income, the marginal tax right here, then, the income and the amount over, which is identical.
And then, the income and then, the actual tax.
So, let’s just run through how to set up the LOOKUP back here.
So, we’d use an @LOOKUP( and then the value we want to LOOKUP.
We want to look up cell B2.
So, I press up the arrow twice, comma, and then it wants to know where the table is.
I would come over here to the table.
And see, I don’t specify the whole table I just specify the first column of the table.
So, it starts with K2.
I press the period and they take me back here to B4 and then I’ll come over.
And I’ll use the down arrow key.
Alright, and now I’m done.
So, I do the closing parenthesis, @LOOKUP(B2,K2…K27).
And then, it will go to the TRUE version of what we all know as VLOOKUP today.
But, it was also cool that it could do in essence an HLOOKUP.
So, here, this is looking up D9 and I specify A1 to seize A17 to C17.
And then, it returns the next row as the lookup table.
Alright.
So, in this case, and when I did the Shark Week podcast a few weeks ago where I showed VisiCalc, I specifically went with a 2x2 table because a 2x2 table could only be treated as a VLOOKUP in Excel today.
It could never be treated as an HLOOKUP.
But, because, Dan Bricklin and Bob Frankston specified only the leftmost column or the top row, only that vector as the table.
It was obvious that it was not ambiguous, whether it was in essence of vertical or horizontal.
Now, it was Lotus 123 that said, “Hey, we have to have more columns here so that we don’t have to have these three tables.
But, then, that forced them to specify either the V or the H. It wasn’t obvious from the way that the formula was up.
Unfortunately, as time went by, LOOKUP remained in the product in Excel.
It now allows multiple columns and we run into that ambiguous expanding range that I ran into.
Mike: Wow, MrExcel, I absolutely love seeing the history of LOOKUP like that, the original LOOKUP functions that Bricklin and Frankston created, whether it was just a single column or a single row.
But, unfortunate that it’s still around.
Oh no, no, it’s totally fortunate because the LOOKUP function has some great uses like looking up the last number in the column.
You give it some big number and it finds the last one.
Looking up a last text item, some big text item, and boom it gets the last one.
Even this one, LOOKUP the last thing whether it’s a number or text.
Sure enough, it finds killer app.
The first killer app, well, you know, it was VisiCalc.
Alright.
That was so much fun.
I can’t wait to do it again.
See you next time!
I’m Bill Jelen from MrExcel.
We’re joined by Mike Griffin from ExcelisFun.
This is episode 106: =LOOKUP vs. @lookup.
Bill: Hey, this is a remnant from VLOOKUP week.
If you remember, I showed VisiCalc.
And that actually prompted an email from Dan Bricklin.
Dan Bricklin, the inventor, or co-inventor of VisiCalc.
And I then, copy Mike Girvin in on my reply and in that reply, I complained because I always complain that =LOOKUP and Excel version of LOOKUP is ambiguous.
And here’s what I’m talking about.
So, we have California.
I need to look up California in this list over here.
So, we do =LOOKUP California within this list, =LOOKUP(A3,F3:G9).
I’m using the array version and that’s it.
I don’t get to specify “,2,FALSE” because LOOKUP is always the TRUE version and lookup always returns the last column in the range.
Well, always, I don’t even get to specify V to specify that the table is vertical.
Excel just figures that out from the shape of the table and that means that LOOKUP can also do the equivalent of HLOOKUP, at least the TRUE version of HLOOKUP.
So, we can LOOKUP that table and that just decides on the shape of the table, whether it’s horizontal or vertical.
Well, that’s kind of a cool trick.
But, what happens when the table is square?
That’s when it’s ambiguous.
Mike: Thanks, Mr. Excel.
Ambiguous, it-- may be somewhat ambiguous.
But, I love the LOOKUP function.
Right here, this is great.
So, it’s taller than it’s wide.
So, we know it’s going to do vertical LOOKUP and I have to just center the LOOKUP value and the table is going to take it from the last column.
Now, the most important thing about LOOKUP is it only does the approximate LOOKUP or TRUE version.
So, right, this has to be sorted.
This one’s great.
Hey, it is sorted.
We know that if you can sort and use approximate match, it’s faster calculating too.
And this is great, you know.
We’re looking this up horizontal.
It’s much wider than it is tall, but it does know what to do with the square.
Anytime, it’s a square table like this, it’ll do VLOOKUP.
So, taller than wide or square does VLOOKUP.
Wider than tall, it does each LOOKUP.
So, =LOOKUP, and a lot of this, I have to just simply select that value in the table, no comma, column number or whatever.
ENTER, =LOOKUP(A13,D13:H17).
Now, of course, this has to be sorted.
Alright, so, back over to MrExcel.
Bill: Okay, alright.
Mike, I hear you.
When it’s square, it’s not ambiguous.
It treats it like a VLOOKUP.
But, here’s my problem.
The one thing that I really like about the LOOKUP function is it returns the last column.
Let’s say-- alright, so, let me edit this formula.
I’m going to make sure this is always looking up $A3.
We’re always reaching back to get the state in column A. And then, I only want to freeze this first A. So, it always starts looking in column A. But then, the range will automatically expand as I go across.
Alright.
Let’s just try this.
I’m going to make a go out of here, see if we can get all the values in yellow.
If I choose a different state, now, Ohio is pulling all of those values from Ohio.
And I feel I really good about this, right.
But then, as the month-- the months go on, and I continue to get more data-- let me just paste some more data here.
And this concept continues to expand right now A through G is 7 columns by 7 rows tall.
Now, all of a sudden, it stops working because at this point, I now have 8 columns and 7 rows.
So, it changes on the fly to an HLOOKUP instead of a VLOOKUP.
That’s what I mean by it’s ambiguous.
Mike: What?
That is amazing.
You’re going to kidding me.
Ambiguous, I can think of a lot of worse words and ambiguous describe that.
So, LOOKUP, when you have an expandable range once you get past the H, it just flat-out switches over.
Now, I’ve never seen that before.
I guess what it means is LOOKUP has lots of great uses.
But, don’t use it with an expandable range because yeah, it’ll switch right over.
You know, we can use the LOOKUP function to do this.
We’ll just use not the array version.
Not that we’re going to use this ARRAY, we use the lookup vector and the result vector, right.
So, the lookup vector, boom, F4 to lock that, comma, and the result vector.
We’ll leave it as a relative cell reference, =LOOKUP($A16,$A$6:$A$12,B6:B12).
And then, copy it over.
I love it.
Look at that, all the way across that little purple range is moving but yeah.
It’s ambiguous and lots of other words.
Alright.
I’m going to throw back over to MrExcel.
Bill: Oh, but, Mike, now, I have newfound respect for VisiCalc which invented LOOKUP.
I mean actually back in the days of VisiCalc, we literally only had 20 functions.
So, here’s the VisiCalc reference card with some MIN, MAX, COUNT, AVERAGE, NPV (net present value), and then, LOOKUP, NA, ERROR, PI, ABS (absolute value), INT (integer), and then a whole bunch of math things.
The EXP, SQRT, LN, LOG10, SIN, ASIN, COS, ACOS, TAN, and ATAN are the only functions, only 20 functions.
So, LOOKUP was one of the original functions and Dan Bricklin mentioned that Bob Franksen wanted LOOKUP in there so that we can do his 1978 tax return.
So, here I have the 1970 tax rate schedules.
Remarkably, all this stuff is online, and I set up a VisiCalc spreadsheet that would let me do this.
Now, there were three elements.
It was, you owed, a certain tax plus a percentage of the amount over the previous rate.
Okay, so, I’ve set up three different tables here with the income, the marginal tax right here, then, the income and the amount over, which is identical.
And then, the income and then, the actual tax.
So, let’s just run through how to set up the LOOKUP back here.
So, we’d use an @LOOKUP( and then the value we want to LOOKUP.
We want to look up cell B2.
So, I press up the arrow twice, comma, and then it wants to know where the table is.
I would come over here to the table.
And see, I don’t specify the whole table I just specify the first column of the table.
So, it starts with K2.
I press the period and they take me back here to B4 and then I’ll come over.
And I’ll use the down arrow key.
Alright, and now I’m done.
So, I do the closing parenthesis, @LOOKUP(B2,K2…K27).
And then, it will go to the TRUE version of what we all know as VLOOKUP today.
But, it was also cool that it could do in essence an HLOOKUP.
So, here, this is looking up D9 and I specify A1 to seize A17 to C17.
And then, it returns the next row as the lookup table.
Alright.
So, in this case, and when I did the Shark Week podcast a few weeks ago where I showed VisiCalc, I specifically went with a 2x2 table because a 2x2 table could only be treated as a VLOOKUP in Excel today.
It could never be treated as an HLOOKUP.
But, because, Dan Bricklin and Bob Frankston specified only the leftmost column or the top row, only that vector as the table.
It was obvious that it was not ambiguous, whether it was in essence of vertical or horizontal.
Now, it was Lotus 123 that said, “Hey, we have to have more columns here so that we don’t have to have these three tables.
But, then, that forced them to specify either the V or the H. It wasn’t obvious from the way that the formula was up.
Unfortunately, as time went by, LOOKUP remained in the product in Excel.
It now allows multiple columns and we run into that ambiguous expanding range that I ran into.
Mike: Wow, MrExcel, I absolutely love seeing the history of LOOKUP like that, the original LOOKUP functions that Bricklin and Frankston created, whether it was just a single column or a single row.
But, unfortunate that it’s still around.
Oh no, no, it’s totally fortunate because the LOOKUP function has some great uses like looking up the last number in the column.
You give it some big number and it finds the last one.
Looking up a last text item, some big text item, and boom it gets the last one.
Even this one, LOOKUP the last thing whether it’s a number or text.
Sure enough, it finds killer app.
The first killer app, well, you know, it was VisiCalc.
Alright.
That was so much fun.
I can’t wait to do it again.
See you next time!
