You read correctly -- 1979. In Bill's last installment for VLOOKUP WEEK 2012, we go back to 1979...VisiCalc and the whole '20 Functions' available in that time! There were no IF statements and there was no VLOOKUP...but there was 'LOOKUP'. Follow along and see where VLOOKUP began!
Transcript of the video:
Hey, welcome back it's another MrExcel netcast.
I'm Bill Jelen.
This is the last of the VLOOKUP week that be appropriate to go back and look at very beginning, the origins of lookup.
So, here I am in VisiCalc, VisiCalc.
This is basically version one of VisiCalc 1979, they only had 20 functions back then, so if you're in VisiCalc, one of the functions is @Sum, we want SUM from this cell, I press the period down to that cell closing parenthesis.
So, it in those 20 functions they did not have an IF statement, they do not have VLOOKUP, but they did have look up and look up was very interesting I actually think that you're going to end up using a lookup in the VisiCalc more often for IF-THEN statements, like I want to check and see if column B is greater than or equal to 80, the heading there isn't, right?
So, we're going to use @lookup open parenthesis and we want to look up the value to the left of us comma and the lookup table the lookup table can either be one column or one row that goes down or across we start at that point and go to that and what it does it automatically returns the value if it's a column, it returns value to the right.
If it's a row, it returns the value just below.
So, this is going to prove down through here it's going to find the 0, it's going to see the 80, it's gonna give us the value of 0 because it's not greater than 80.
Now, in the next one here let's just try it out here he will change this to 80, see that then it returns the value greater than or equal to.
So, it's doing kind of the true version of the lookup, but only allows for a two-column table and I actually again as I mentioned I think visit only something that we're going to use more often or in IF statement.
So, I'm going to do slash R here to replicate that's the source range the target range is going to be from that cell period down to this cell and then check this out what they're doing is, they're going through the formula that I'm copying and for each part they want to know if it's a relative reference or no change.
So, right there B2 is relative, so press the F2 the top of the range is no change, so N and the bottom of the range is no change, so we get our answers right at that very first lookup function.
Now, this was programmed by Dan Bricklin and Bob Frankston.
I actually talked to Dan Fylstra who was our email with Dan Fylstra this week who was president of VisiCorp.
He said, they had look up, it wasn't until Lotus 1-2-3, that VLOOKUP came around and again exchanging those with Mitchell Kapor and Jonathan Sacks and Jonathan says as far as he knows he is the inventor VLOOKUP.
So, for those of you who love VLOOKUP here's a look back at the very beginning, the very first spreadsheet VisiCalc and how they implemented lookup was it, by the way the first example, I did for this was a grade book where we had you know, C word of 64 and F 65 to 69 is it E, but it returned all zeros this lookup can only return numeric value.
So, you could return the letter grade A, B, C, D and so on.
Great look back into history, want to thank everyone for who participated in VLOOKUP week.
We obviously struck a chord lots of interest and that was lots of great articles from across the web.
Great to see you want to do it again next year.
Well! Hey, I want to thank you're stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
This is the last of the VLOOKUP week that be appropriate to go back and look at very beginning, the origins of lookup.
So, here I am in VisiCalc, VisiCalc.
This is basically version one of VisiCalc 1979, they only had 20 functions back then, so if you're in VisiCalc, one of the functions is @Sum, we want SUM from this cell, I press the period down to that cell closing parenthesis.
So, it in those 20 functions they did not have an IF statement, they do not have VLOOKUP, but they did have look up and look up was very interesting I actually think that you're going to end up using a lookup in the VisiCalc more often for IF-THEN statements, like I want to check and see if column B is greater than or equal to 80, the heading there isn't, right?
So, we're going to use @lookup open parenthesis and we want to look up the value to the left of us comma and the lookup table the lookup table can either be one column or one row that goes down or across we start at that point and go to that and what it does it automatically returns the value if it's a column, it returns value to the right.
If it's a row, it returns the value just below.
So, this is going to prove down through here it's going to find the 0, it's going to see the 80, it's gonna give us the value of 0 because it's not greater than 80.
Now, in the next one here let's just try it out here he will change this to 80, see that then it returns the value greater than or equal to.
So, it's doing kind of the true version of the lookup, but only allows for a two-column table and I actually again as I mentioned I think visit only something that we're going to use more often or in IF statement.
So, I'm going to do slash R here to replicate that's the source range the target range is going to be from that cell period down to this cell and then check this out what they're doing is, they're going through the formula that I'm copying and for each part they want to know if it's a relative reference or no change.
So, right there B2 is relative, so press the F2 the top of the range is no change, so N and the bottom of the range is no change, so we get our answers right at that very first lookup function.
Now, this was programmed by Dan Bricklin and Bob Frankston.
I actually talked to Dan Fylstra who was our email with Dan Fylstra this week who was president of VisiCorp.
He said, they had look up, it wasn't until Lotus 1-2-3, that VLOOKUP came around and again exchanging those with Mitchell Kapor and Jonathan Sacks and Jonathan says as far as he knows he is the inventor VLOOKUP.
So, for those of you who love VLOOKUP here's a look back at the very beginning, the very first spreadsheet VisiCalc and how they implemented lookup was it, by the way the first example, I did for this was a grade book where we had you know, C word of 64 and F 65 to 69 is it E, but it returned all zeros this lookup can only return numeric value.
So, you could return the letter grade A, B, C, D and so on.
Great look back into history, want to thank everyone for who participated in VLOOKUP week.
We obviously struck a chord lots of interest and that was lots of great articles from across the web.
Great to see you want to do it again next year.
Well! Hey, I want to thank you're stopping by.
We'll see you next time for another netcast from MrExcel.
