A couple of weeks ago, I unwittingly stumbled across a serious contender for coolest Google product feature: GoogleLookup. GoogleLookup is a Google Spreadsheets function that attempts to return the value of any attribute for any entity that you specify. Here’s the syntax: =GoogleLookup(”entity”, “attribute”).
For those of you not familiar with functions in Google Spreadsheets (the function-savvy may skip to paragraph 3), here’s a really simple example of a more conventional function: sum. In any spreadsheet cell, if I enter =SUM(3+5) and hit the “return” button, the content of the cell will display 8. Functions can also refer to values already in your spreadsheet, which is what makes them particularly useful. For instance, the function =SUM(A2:A20) will instantly sum all of the values in column A, rows 2 through 20. If you want to try this yourself, go to http://docs.google.com/ and open a new spreadsheet. You can also learn more about functions in the Google Docs Help Center.
So what makes GoogleLookup really cool is that it references data not just from your spreadsheet (as with functions like =SUM(A2:A20)), but searches that incredibly expansive body of published information: the world wide web. By performing a targeted websearch, GoogleLookup attempts to return some fact about the entity you specify. City populations are a nice example. Entering =GoogleLookup(”Philadelphia”, “population”) in any spreadsheet cell…
screenshot: GoogleLookup function
screenshot: GoogleLookUp loading
returns the value 1,449,634, which is in fact accurate. This may sound like magic, but go ahead and try it in a spreadsheet of your own. Now try swapping out “Philadelphia” for other city names: New York, Boston, San Francisco, Bangkok, London…
Radical, no? Perhaps you are saying to yourself, “this seemingly magical feature is indeed rad, but what real world scenario would actually warrant use of this function? How can GoogleLookup help me?” Let’s look at one example.
My good friend Caroline recently asked me and Dan to recommend some awesome dance songs for her upcoming wedding. Needless to say, Dan and I had lots of suggestions. Rather than rattle them off while she took notes on a scrap of paper, I told Caroline that I would send her a spreadsheet. When I got home, I opened a new spreadsheet and typed out 26 dancable song titles. But I wanted to give Caroline a little more information — I wanted to include the artist of each song. I didn’t know the artists for some of the songs on the list (especially for some of the more questionable tracks, all suggested by Dan) but I could have looked each one up and then typed out the artists name. This might have taken 20 minutes. Instead, I used GoogleLookup to automate that process.
Take a look at the spreadsheet. In cell B2 (under “artist”) I typed =GoogleLookup($A2, “artist”). (ignore the $ symbol; it just means that I want the function to use column A even if I copy the formula into another column). I then copied this function into all of the cells in column B (just drag the lower right-hand corner and drop). Within moments, all of those cells were populated with likely values for the attribute “artist” given the entity in column A, the song title.
Now, as you will observe, the values are not perfect. Gloria Trevi is NOT the artist I had in mind when I suggested the classic rock tune, “Gloria.” And while UB40 did do a version of “Can’t Help Falling in Love With You,” I would urge Caroline to stick with the Elvis original. But there were also a lot of accurate values. And one cool subfeature of GoogleLookup is that clicking into the cell will trigger a pop-up citing the source (url) of the value and offering a “more options…” link.
screenshot: click into cell for more info
Clicking “More options…” will give you up to three alternative values (and the sources from which these values come).
screenshot: more options for GoogleLookup
You can easily change the displayed value by selecting one of these alternatives.
As you can see on my Good Dance Songs spreadsheet, I used GoogleLookup to populate two more columns: album and year, both of which will be helpful to Caroline and her DJ (the functions, respectively: =GoogleLookup($A2, “album”), =GoogleLookup($A2, “year”)).
So next time your boss hands you a list of US states and asks you to fill in the capital of each state by EOD; or a list of years for which she would like the Academy Award winner for “Best Film”; or a 200 row list of NBA players, each of which needs a “height”… don’t fret, use GoogleLookup to quickly get the data you need.
(Also, if you haven’t already, now may be a good time to read my disclaimer)
Nice post, Beah. One question: how is it possible that Phoenix is larger than Philadelphia?
Great read. I’ll check it out, might come in handy!
PS: I found no other way of contacting you, so i post it here: There’s a typo in your disclaimer. It reads techniligies, instead of technologies. Feel free to edit my comment out
Best of luck!
Thanks for pointing out the typo, Maarten! Fixed
“Can’t Help Falling in Love You” is not an Elvis original. Elvis had zero original material-all covers. Just sayin’
someone please alias this as gl( … )
Would love to see MS add this feature to Excel!
Too neat! I’m going to teach my 8 year old son how to use this. Can you imagine the possibilities at school?
Hi,
nice posting, thanks!
One little glitch: when one copies your syntax directly into a spreadsheet, it uses the wrong quotes and gives an parser error (at least in FF on Mac OS X). Maybe you could fix this?
regards,
Tim
This is so cool, the possibilities are endless. This could be really useful for research projects.
You write up song lists on spreadsheets?!
Seriously though, pretty cool feature
=GoogleLookup(”Philadelphia”; “population”) has a minor error it should be
=GoogleLookup(”Philadelphia”, “population”)
Hey Dan, the reason Phoenix is larger than Philadelphia is because more people live there. :’)
son. phoenix just topped philly. 5th largest in the nation. recognize.
Next, you need to teach us how to best use Google Squared!
Really great post.
I’m not usually a commenter but I had to let you know how cool I thought this was.
Thanks so much!
we do use spreadsheet a lot. but never in that extend
Another cool feature google spreadsheets has is the live form feature.
I used to get so messed up when i needed to compile data from 10 different people, and some would get lost in translation because they didnt all fill it out to the format i wanted, so i had to change it to the format i needed, from the messages they’d send me.
Now all i do is email them a form to fill out every time i need their input, and voila! get all the data in my spreadsheet without even copying it there.
All that’s left for me to do is check authenticity and i’m done!
Google never ceases to impress.
I really liked this function =GoogleLookup(”Philadelphia”; “population”)
My only reservation is accuracy – anyone checked population figures ? The figures “look” a bit high? I will check at some point but a great article nonetheless – thanks.
The population data are consistent with 2008 U.S. Department of Census sources. In fact, some northeastern cities are experiencing a net decline in population due to the migration of jobs out of the region and development of new industries in the sunbelt states. Seven of the top ten cities by population are in California, Arizona and Texas.
That spreadsheet of so-called dance tracks wouldn’t create an atmosphere in a bell-jar full of mould. I’d hate to have been at that wedding, looks like you had the world’s most boring disco, ever, ultra, part three.
Next time, I suggest you hire a professional.
Seems cool enough, although i have to agree with Robert about the accuracy…
PS And comment design looks broken to me(i am on FF3)
Texas/houston is huge… plus the immigrants from mexico also the right wing policy that rules the area shuns on birth control and abortion.
This is a great tip. I’m an Excel Geek and I’ve never seen this before, but you can bet I’ll find a use for it now. I can’t function without my spreadsheets – grocery list, budget, checking, Christmas list; you name it – I’ve got a spread sheet for it. Thanks for the post!
phoenix is still annexing their suburbs, and philly is not. philly is still the 4th largest metro area/tv market, behind nyc, la, and chicago.
In fact, some northeastern cities are experiencing a net decline in population due to the migration of jobs out of the region and development of new industries in the sunbelt states.
————–
it’s not due to migration of jobs out of the area. people move to warmer weather, and the jobs just follow the human capital.
This is a dangerous feature to have in a spreadsheet, especially if this is going to be used to make decisions. The primary concern is the lack of validity in the results searched (lookup). While, studies have shown that wikipedia does have accurate information, the accuracy can not be guaranteed. I can go in and change the population of Phoenix to -1,000 (negative 1,000) and your spreadsheet will show the population to be exactly that. Before that figure is corrected, decisions might have been made.
All the same, this feature can be used to generate an initial list and the lookeup information can be verified from other sources. Thus, it might cut down on the total time to use this type of information.
Clearly, a lot more needs to be done in this area. The web is still not structured enough to get lookups. I tried looking up prison population, number of hospital beds, wages, etc. and always got the #N/A result. The canned result of city population that works, was ,well, just canned.
Is the thing still working? I put in a function like =GoogleLookup(”Dallas”,”population) and when I leave the cell, it says “Loading…” then never does anything.
…just to clarify my earlier post, that was “population” in there.
lol this thing isn’t accurate at all. Try putting in Macon, GA – it throws back a population of 320,000. Umm no try 85,000. I also put in Pensacola, it came back with 56,000 – try again – more like 300,000.
Neat idea but so far my first 2 examples are way, way off.
Seems to be working now. Earlier problems with “Loading…” never disappearing seem to have disappeared.
Dale – actually, it returned valid numbers, or at least as valid as yours. The number returned for Macon, GA is the population of the Macon Metropolitan Statistical Area, while the number you present is the population of the city itself (and actually, last estimates from the US Census Bureau are 93,076). The number returned for Pensacola is the population of the city itself, while the number you mention is for the Metropolitan Statistical Area (and actually, according to the most recent estimates of the US Census Bureau, is actually 453,451).
This is great. I tried it with UK populations and it gave good values – as good as wikipedia anyway!
You can type the name of a language instead of ‘population’ in the example above and it makes a good guess at the translation. eg =GoogleLookup(A1,”french”)
nerds
Nice outside the interface thinking.
Very cool , thanks for sharing this info , this could come in very handy to look up things
=GoogleLookup($A2, “artist”) This function giving me error message like
#ERROR!
Dan, Phoenix the city is more populous than Philadelphia the city because, as noted, Phoenix is still swallowing its suburbs and physically expanding, while Philadelphia is bounded. However, the Philadelphia metropolitan area is much larger than the Phoenix metro area–three or four times as big. There’s not much around Phoenix besides Phoenix, while Philadelphia’s gravitational pull extends very far in many directions and absorbs entire additional cities, such as Camden.
Gryndyl,
Elvis didn’t write his own songs, but many of his songs were written specifically for him. “Can’t Help Falling in Love with You” is based on an eighteenth-century French romantic composition, but with new lyrics written for Elvis. (Elvis is listed as co-writer on “Heartbreak Hotel”; the primary authors of the song, Thomas Durden and Mae Boren Axton, “conceded that he did not recognize the song after Elvis had made the changes to the song in the studio, including changes to the tempo, phrasing, lyrics, and overall sound”, to quote Wikipedia.)
Google is taking over the world, with great products. Must have Microsoft wondering about their business model.
Hey all
I think Google is not as great as we all might think. Google is smart and seizes opportunities faster then other. It’s own products have not been as successful as the one’s it bought. Although the application discussed here is good and an even better demonstration using screen shots. My comment was for the various posts on how good or bad Google is and various errors in it.
This has been in Excel forever. See these functions: LOOKUP and VLOOKUP.
Nice to see it in Google Spreadsheets though. Very handy.
This probably won’t work by the time that anyone else gets to try it, but the response to this formula made me chuckle!
=GoogleLookup(”Universe”, “age”)
The response:
“5 years and up”
Jacine, — I think Excel’s lookup functions are quite different in that they only search data from the spreadsheet you are working on, not from external sources (the web).
What a fantastic feature. do you think you could get it to return a value for London Stock Exchange prices. i seem to get error: Not a number: googlelookup(a1,”shareprice”,”lse”)after trying all different combinations i still cant get it to return a value
Scrub that post, i have solved the problem with =GoogleFinance(”symbol”; “attribute”)
thanks for the idea though
What does Google have against Austin? When I try to find Austin’s population I get “N/A”. I did a lookup on “Texas”, “capital” which returned Austin. I then passed that cell into my GoogleLookup for population and came up blank again. “Austin, TX” and “Austin, Texas” did not work either.
Very cool! This has definite time-saving potential!
I got your blog site while searching for help on the google docs site. Can you help? My google spreadsheet is not allowing me access to manipulate the site. it appears almost as if it randomly switched me to a “view only” status. I’m the owner of the site so that is odd. All of those that i allow access to the site (in edit and view only mode) are not having problems at all. My problem is how do i get back to edit mode?
i realize your blog owner and not with google but i can’t seem to find a “help” email on the google site anywhere. If you can help me, great, if not, no biggy – thanks!
Can’t say I knew you, but we miss you over here in Philly!