futuremark 3dmark vantage professional xilisoft 1click dv to dvd smartsoft smartftp home 3.0 thegrideon access password professional 2.0 dtsearch desktop 7 ashampoo office 2008 pixarra twistedbrush pro studio 15 grahl pdf annotator 2 graphisoft archicad 12 corel draw graphics suite x4 ashampoo winoptimizer 5 xilisoft audio converter 2.1 ashampoo burning studio 7 zonealarm antivirus 8 lavalys everest ultimate 4.5 adobe flash cs3 professional daz bryce 6.1 mac pctools spyware doctor 5.5 adobe dreamweaver cs4 iexpert registry clean expert 4.58 microsoft expresion web 2 quarkxpress 8 mac adobe creative suite 3 design premium microsoft windows vista business (32bit) futuremark 3dmark '03 pro autodesk 3d studio max 2008 autodesk autocad raster design 2009 uniblue registrybooster 2009 adobe illustrator cs4 mac ashampoo uninstaller 3 acdsee pro 2 microsoft sharepoint designer 2007 paragon hard disk manager 2008 professional intuit quicken rental property manager 2009 futuremark 3dmark '05 pro ca erwin process modeller uniblue speedupmypc 2009 zonealarm internet security suite 8 microsoft office project professinal 2003 fix-it utilities professional 9 microsoft office 2008 mac systemssuite professional 8 mathworks matlab r2008a powerarchiver 2009 futuremark 3dmark '06 advanced microsoft mappoint 2009 north america steinberg nuendo 3.2 adobe captivate 3 adobe after effects cs4 xilisoft cd ripper zonealarm pro 8 microsoft digital image suite 2006 adobe creative suite 4 master collection mac faronics deep freeze enterprise 6 native instruments traktor dj studio 3.4 mac codegear rad studio 2007 architect native instruments reaktor 5 autodesk autocad map 3d 2009 corel draw 11 mac adobe director 11 norton partitionmagic 8.0 microsoft visual studio 2008 professional fl studio 8 xxl efreesky magic utilities 2008 video-studio-pro-x2.html">corel video studio pro x2 native instruments reaktor 5 mac paragon partition manager 8.5 enterprise server adobe creative suite 4 design premium adobe presenter 7 mcafee total protection 2009 autodesk autocad revit structure suite 2009 hd tune professional tuneup utilities 2008 daz bryce 5.5 adobe illustrator cs4 autodesk autocad revit architecture 2009 quarkxpress 7.3 passport propellerhead reason 4 vmware workstation 6.5 abbyy finereader 9.0 professional autodesk autocad mechanical 2009 codegear delphi for php 1.0 adobe photoshop cs4 extended mac zonealarm internet security suite 2009 adobe flash cs4 professional mac elcomsoft advanced archive password recovery 4 professional ashampoo photo optimizer 2 pantaray q-setup pro 9 microsoft mappoint 2006 europe adobe after effects cs3 professional ashampoo firewall pro acronis true image 11 home cyberlink powercinema 5 microsoft works 9 crystalidea uninstall tool 2.5 pc washer 2 windows xp professional sp3 adobe contribute cs4 tamosoft commview for wifi 6 full mylogomaker professional 2 ashampoo magical snap 2 installshield x express edition ashampoo clipfisher video-to-audio-converter-5.1.html">xilisoft video to audio converter 5.1 adobe flex builder professional 3 ashampoo powerup 3 efreesky magictweak 4.11 transmagic expert vmware workstation 6.5 ace adobe premiere pro cs3 cyberlink powerproducer 5 ultra openpim imsi turbofloorplan home and landscape pro 12 xilisoft audio maker 3 adobe creative suite 4 web premium microsoft money 2007 deluxe ashampoo cover studio paragon partition manager 9 professional tamosoft commview 6 full microsoft windows vista business (64bit) adobe indesign cs3 cyberlink power2go 6 adobe creative suite 4 master collection adobe indesign cs4 mac sound forge audio studio 9 chemtable reg organizer 4.21 i.r.i.s. readiris pro 11 mac novosoft handy backup 6.1 pro ashampoo winoptimizer 4 novosoft handy backup 6.1 server adobe incopy cs4 xilisoft iso burner aglare dvd ripper platinum 6 algolab photo vector 1.98 guitar pro 5 roxio creator 2009 ultimate xilisoft dvd ripper platinum 5 microsoft office onenote 2003 canvas 11 with gis+ corel photoimpact x3 autodesk navisworks review 2009 adobe acrobat 9 pro extended autodesk autosketch 9 adobe indesign cs4 adobe premiere pro cs4 mac runtime revolution enterprise 2.9 autodesk navisworks simulate 2009 microsoft frontpage 2003 microsoft office visio professional 2007 adobe after effects cs4 mac corel painter x mac propellerhead reason 4 mac imsi turbocad pro 15 microsoft money 2007 home & business autodesk navisworks manage 2009 adobe photoshop cs4 extended autodesk 3d studio max 2009 microsoft windows vista ultimate (64bit) burnaware professional autodesk combustion 4 adobe dreamweaver cs3 adobe flash cs4 professional ashampoo core tuner cakewalk sonar 7 producer edition sony acid pro 6 futuremark pcmark vantage advanced adobe soundbooth cs4 microsoft office visio professional 2003 roxio copy & convert 3 nuance omnipage professional 16 acronis disk director suite 10 native instruments traktor dj studio 3.4 quarkxpress 7.3 mac conceptdraw office 8 adobe fireworks cs4 adobe premiere pro cs4 quarkxpress 8 e-gadgets delete duplicate files adobe photoshop cs3 extended powerdesk pro 7 aurora media workshop autodesk 3d studio max design 2009 acdsee 10 photo manager autodesk autocad electrical 2009 intuit turbotax premier 2008 microsoft windows vista ultimate (32bit) adobe creative suite 3 master collection acdsee photo editor 2008 codegear rad studio 2009 architect cyberlink powerdirector 7 ultra daz carrara 6 pro mac i.r.i.s. readiris pro 11 autodesk autocad civil 3d land desktop companion 2009 innovative solutions advanced uninstaller pro 9.5 smith micro poser 7 video-converter-ultimate-5.1.html">xilisoft video converter ultimate 5.1 activestate komodo ide 5 adobe dreamweaver cs4 mac paragon drive backup professional 8.5 corel dvd moviefactory 6 plus pixologic zbrush 3 mac activestate komodo ide 4.4 autodesk autocad revit mep suite 2009 sony cd architect 5.2 web page maker 3 ashampoo photo commander 6 xilisoft dvd ripper ultimate 5 guitar pro 5 mac cyberlink powerdvd 8 ultra adobe creative suite 3 web premium 3d home architect design suite deluxe 8 originlab originpro 8 avid media composer 2.8 partition commander server edition 10 microsoft office 2003 professional microsoft office project professional 2007 autodesk autocad 2009 ashampoo movie shrink and burn 3 nero 9 apple final cut express 4 mac microsoft streets and trips 2009 cyberlink dvd suite 5 pro symantec winfax pro 10.4 ashampoo magical defrag 2 daz bryce 6.1 videoeditmagic-4.3.html">deskshare videoeditmagic 4.3 microsoft office enterprise 2007 wincare memory booster gold autodesk inventor professional 2009 sony sound forge 9 autodesk autocad architecture 2009 imsi turbofloorplan landscape and deck 12 ashampoo burning studio 8 acala dvd ripper professional 5 smartsound sonicfire pro 5 scoring corel painter x nuance paperport professional 11.1 nuance pdf converter professional 5 sony vegas pro 8 microangelo toolset 6 dzsoft perl editor 5.8.3 modelright professional 3.0 nero 8 ultra edition pinnacle studio 12 ultimate neobyte titan backup microsoft autoroute 2007 europe lavasoft ad-aware 2008 pro microsoft encarta premium 2009 kingsoft office 2009

GoogleLookup: do things you never thought possible with spreadsheets

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 function

screenshot: GoogleLookUp loading

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

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

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)

58 Comments

  • Nice post, Beah. One question: how is it possible that Phoenix is larger than Philadelphia?

  • Maarten says:

    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 :)

  • Gryndyl says:

    “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?

  • Tim Büthe says:

    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

  • SEO Agency says:

    This is so cool, the possibilities are endless. This could be really useful for research projects.

  • larrybn says:

    You write up song lists on spreadsheets?!

    Seriously though, pretty cool feature

  • mbhullar says:

    =GoogleLookup(”Philadelphia”; “population”) has a minor error it should be
    =GoogleLookup(”Philadelphia”, “population”)

  • whoknew says:

    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.

  • IcePick says:

    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”)

  • hull city says:

    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.

  • Richard Lucas says:

    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

  • John Mayson says:

    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!

Leave a Reply