Guide to Excel VLOOKUP basics and top five rookie mistakes

VLOOKUPs Work Like Phone Books

Following on from our time saving Excel shortcuts, we continue offering updated advice for the time-sensitive spreadsheet enthusiast.

Back in 2013 John Gagnon wrote a very popular post about VLOOKUP basics and rookie mistakes.

We thought we’d update the piece to reflect some minor changes for accessing the functionality to VLOOKUP words and values in Excel 2016.

An Excel VLOOKUP can be a marketer’s best friend because it can save you hours of work. Give this formula the information you have (a name) and it looks through a long list (list of names) so it can return the information you need (phone number).

The problem is we often struggle to remember how to use the formula – or worse make mistakes.

We’re going to fix that now. This post will explain:

  • How VLOOKUPs work.
  • Using ‘Tell me‘ to access VLOOKUP functionality in Excel 2016.
  • Five rookie VLOOKUP moves to avoid.
  • Limitations you might encounter.

Many of the tips are courtesy of John Gagnon, and are accurate as of September 2016.

How to use a VLOOKUP

Remember phone books? Phone books happen to give us a fantastic mental model to understand how VLOOKUPs work.

Basically, the phone book is a long list of just a few columns: names and phone numbers. You pick up a phone book with a clear intention – find a phone number (info you want) for a specific person (info you have).

Once you’ve found the person you’re looking for, you look at over to the second column to find their phone number. Call made, problem solved.

It turns out this is the same principle for how a VLOOKUP works. Let’s breakdown what each piece of the formula to understand what they mean:

VLOOKUP Breakdown

There is an added piece of information needed for a VLOOKUP called a range_lookup. This basically is how accurate you want your results.

Excel 2016: Using ‘Tell Me‘ to access VLOOKUP functionality

Excel 2016 comes with a new multi-purpose search box, the ‘Tell me what you want to do‘ tool. Click the box, or ALT + Q to jump right to it. From there, if you type ‘VLOOKUP‘ or any lookup or reference search term, really, then the function you need will appear in a dropdown menu.


For the purpose of this article, I want to VLOOKUP a value. Selecting this then offers the ‘Function Arguments‘ box where you can add in the Lookup_value and Table_array etc.


As you can see, it’s a little more helpful for newer users than in previous editions of Excel.

Five rookie VLOOKUP mistakes to avoid

Realizing VLOOKUPs work the same as a phone book is helpful. It’s also helpful to know the common mistakes. Here are the top five mistakes made by VLOOKUP rookies.

1. Not having Lookup_Value in first column of your table array

VLOOKUPs only work when the info you have (lookup-value) is in the first column of data you’re looking at (table array). To use the phone book, you need to start with a name first. You can’t start with the phone number and find the name.

Lookup Value Must be in First Column

2. Counting the wrong number of columns for Col_index_num

Once Excel has found the value you gave it, it needs to know what give you back. This comes in form of a column number. Make sure to start counting from the first column of the range (table array).

Counting Wrong Number of Columns

3. [Range_Lookup] Not using FALSE for exact matching

Many marketers get the wrong values because they forget one step. Ninety-nine percent of the time we want exact match, which means a value of FALSE (here’s why).

Must Use FALSE for Exact Matching

4. Forgetting absolute references (F4) when copying the formula

The power of a VLOOKUP is it can be copied down to hundreds or thousands of cells. But once you copy this down, the references change leading to errors. To fix this issue convert your range to an absolute value instead of a relative value, so cells don’t move around (as they tend to do).

5. Extra spaces or characters

Occasionally when data is copied from one source to another, a few leading or trailing spaces tag along. This causes issue during the match, so use TRIM to delete any spaces added to the cell (except for any single spaces between words).

VLOOKUP 201: The John Smith problem and going left

After you use VLOOKUP enough, you’ll encounter its limitations. For example:

  • It only returns the first match it finds, even if there are hundreds of possible matches.
  • It can only return a value in the table array to the right – it can’t go left!

(There are simple solutions to these problems, creating unique keys and pasting – but we’ll save those for another time.)

Back to the phone book for a moment. How many John Smiths are listed? Probably more than one. But with a VLOOKUP, only the phone number of the first John Smith is being returned! You’re probably calling the wrong guy.

To make sure you’re calling the right John Smith, you need to bring in additional information. Commonly for phone books it’s an address (e.g., John Smith at 123 Acme Lane or John Smith at 765 NW Jones St.).

Again, it’s the same for VLOOKUPs.

Let’s say you want to know match type by keyword. Your match type column would be identical (all “broad”, in this case), and your second variable (keyword) would differentiate the data.

Create a new table with both pieces of data in columns, and insert “&” in the table_array field of your VLOOKUP. Then the VLOOKUP knows to return the combined data for your result.

What are your most common uses for VLOOKUPs? Is there anything about VLOOKUPs that stump you?


The natural evolution of digital for brands: becoming more human

It used to be that digital interactions portrayed in the Jetsons, on Star Trek and through KITT on Knight Rider were make-believe preserved for the television and big screen.

However, with the rise of digital personal assistants in recent years like Cortana and Siri, and intelligent bots, what was once science fiction is quickly becoming fact.

We are on the cusp of the next big shift in computing—a shift that is fueled by the advent of artificial intelligence (AI) and built around the one act that comes most natural to us—conversation.

We are optimistic about what technology can do, and this is rooted in a belief that every person and organization should be empowered to achieve more. It’s important though to set some context on how we’ve arrived at this new reality to help answer why you should care and what you should do as a marketer.

Every decade is marked with a shift driven by technological innovation. From the proliferation of PCs during the 80s to the emergence of the Web in the 90s to the rise of mobile and the cloud in the last decade – we have expanded our commerce, improved our communication and strengthened our connections.

But while these advances have helped the world become smaller, in many ways they’ve added layers of complexity, and more significantly, they’ve put the onus on us, the user, to adapt our behavior and expression so that we can be understood by the machines.

But what if we could just talk and interact with technology in exactly the same way we do with other people? Call upon it when we genuinely need support and not have to change the way we behave in order to reap the benefits?

We envision a world where digital experiences mirror the way people interact with one another today. A world where natural language will become the new user interface. A world where human conversation is the platform—the place to discover, access and interact with information and services, and get things done.

Computing is becoming more human

The mobile first, cloud-centric world of computing is driving this new platform of engagement. With the ability to understand tone of voice, interpret emotions and remember conversations, the nature of AI is no longer about man vs. machine, it’s about machines complementing and empowering people to do more of what really matters to them.

One of the major trends that has set the stage for an era of people communicating with their devices is the growth in people communicating through their devices.

With more than 3 billion people using messaging apps every day, consumers are spending five times longer on average using messaging apps than they do on all other mobile apps.

And, what’s more, we’ve reached a point where natural language is the new universal user interface with technology. Search intelligence is now embedded across platforms and services, harnessing intent understanding and using a vast base of semantic knowledge.

When coupled with machine learning that’s infused throughout all of our digital interactions, technology is becoming more human. People and machines are able to sustain conversations with personal digital assistants and intelligent bots in such a way that the meaning, intent and even emotion behind the words are as comprehensible as the words themselves. And we’re nearing a time when this will be scaleable to every individual and every business.

Conversations are the new platform

Imagine if brands had the opportunity to engage with consumers in ways that were not only relevant and personal but also in an environment where their added value is proactively sought out by the individual themselves. In the realm of conversations as a platform, this is the new reality.

Picture having a sudden craving for pizza. Just by telling the personal assistant on your phone “I would love a thick crusty four seasons pizza right now,” you’ve initiated a conversation to seek options or gone directly to your preferred pizzeria.

The bot you choose to talk to will place the order for you and arrange delivery and payment within seconds. In essense, you expressed a need, an emotion and took the initiative to engage with a particular company through their bot and you got what you wanted with more speed and ease than was previously possible.

This example illustrates how brands need to be ready for the engagement opportunities within this conversational exchange.

Marketers must think strategically about the best framework for building bots that cater to natural language and play into conversations across all platforms.

From there they must figure out how to connect these into their existing cloud, CRM and other elements of their digital marketing ecosystem. This is a business strategy that will require input across functions – starting with the CMO and throughout marketing, sales and IT – in order to offer a customer experience that is consistent with other more traditional digital engagements as well as being timely, relevant and serendipitous.

It’s a time of fundemental change in the digital technology experience. Conversations as a platform, A.I. and emerging search technologies are advancing this next frontier in ways that will simplify and enhance our lives and will be critical to every future marketer’s success.

To put a twist on these three timeless words from the Cluetrain Manifesto, it’s not just “markets are conversations,” but the future of “marketing is conversations.”

Ryan Gavin is General Manager, Search and Cortana Marketing at Microsoft.


Six most important search marketing news stories of the week

women underrepresented

Welcome to our weekly round-up of all the latest news and research from around the world of search marketing and beyond.

This week we’ll mostly be talking about Penguin 4.

What do you mean you haven’t seen the first three? Well don’t worry, I’ll bring you up to speed. In the first instalment, a terrible tragedy befalls a young penguin at Camp Crystal Lake while the teenage camp counsellors weren’t paying attention.

Then 21 years later, the Penguin returns… hang on I may have the wrong Wikipedia tab open here… I’ll get back to you.

Penguin 4.0 news round-up

As Google confirmed last Friday, Penguin 4.0 is now being rolled out. The spammy link punishing update now works in real-time and will only penalise offending webpages rather than the entire domain.

Penguin recovery time:

It seems Penguin recoveries are happening right now, as Google’s Gary Illyes confirmed on Twitter…

@atmoore81 it’s happening as we speak. or tweet. it will take a few more days to finish that part

— Gary Illyes (@methode) September 28, 2016

It’s also worth noting that Penguin 4.0 has been coming together gradually over time, with the main component rolling out on the 23rd.

2/? @rustybrick we choose that announcement date because it’s just hours away from the most noticeable part’s launch time. In fact we said..

— Gary Illyes (@methode) September 28, 2016

Expert view on Penguin 4.0

This week we talked to a few SEO practitioners to get their expert views on Penguin 4.0. and for some advice for those who wish to avoid being affected by Penguin.

Kevin Gibbons: Focus on building a brand, not links. Aim to tell your story via content, data-driven analysis and knowledge – and amplify to a targeted audience via multiple channels; social media, paid search, digital PR etc…

Gerald Murphy: Data analysis is even more important to SEO. This most effecient way to analyse this update is to breakdown links by category, sub category, and page level, and then compare this with data, such as, visits, average blended rank, and revenue, for example.

Nikolay Stoyanov: Forget about shortcuts in SEO! There aren’t any. The only way to stay on the safe side and secure your brand, visitors and sales is if you do white hat SEO. Write well researched and useful content and build quality links to it.

Just 36% of people entering the tech industry are women

As Luke Richards reported this week, the technology industry is lagging behind many other sectors when it comes to the proportion of women taking up entry level positions.

McKinsey’s report, Women in the Workplace, surveyed 132 companies which collectively employ more than 4.6m people. It shows that while 75% of CEOs in corporate America are saying gender equality is a top ten priority, tech is still woefully behind.

More online product searches start on Amazon than Google

As reported by Graham Charlton this week, Amazon is beating Google for product searches.

The second annual State of Amazon study by BloomReach found that 55% of consumers start their online product searches on Amazon, compared to 28% who opt for a search engine.

The survey of 2,000 US consumers found that Amazon’s share of the action was up 11% year on year, and the figures down for search engines and other retailers.

amazon beats google

Google AdWords introduces cross-device remarketing for TV ads and offline sales

Google AdWords has released a new product designed to “close the loop between TV and digital” with Brand Lift.

According to the AdWords blog post, Brand Lift will be able to show marketers how TV ads increase Google and YouTube searches for brands compared to YouTube campaigns.


There’s little to set up, you just need to be running Brand Lift on both your TV campaign and YouTube campaign, and Google will report the searches for your brand.

Google is also introducing location extensions and store visits measurement for the Google Display Network to help marketers “close the loop between online ads and offline sales.”

As people browse specific websites or interact with their apps, brands will be able serve ads that show a business address, Google Maps directions or photos.

AdWords announces improved remarketing lists for search ads (RLSA)

In a busy week for AdWords, the paid search platform has announced improvements to RLSA to give you more flexibility on how you adjust search ads, bids, and keywords.

  • Reach your customers across devices. If someone visits your website on their laptop or tablet, you can now reach them with more relevant ads when they search on their phone.
  • Keep site visitors in your list for 540 days. This longer membership duration makes it easier for businesses with seasonality or high consideration products to reach their customers
  • Add your remarketing lists at the campaign level later this year, making it faster and easier to use RLSA. This will work for Customer Match too.


Online Marketing News: Bird’s Eye View, FTC Skepticism and Facebook for Business


Infographic: Latest Twitter Study Sheds Light on Just How Well Video Is Doing
Based off of Twitter’s annual Online Video Playbook, this infographic shows what’s cleverly referred to as ‚The Bird’s Eye View of Video‘ on Twitter. For example, 93% of the video views on Twitter are on mobile. Users who watch video want breaking news, information, and viral content the most, followed by entertainment and celebrity content. AdWeek

The FTC Is Skeptical When Celebrities Are Paid to Like Your Product
The FTC is investigating a few big name brands for bending the rules that govern paid celebrity endorsements. Of course, rules regarding endorsements from celebrities aren’t news to marketers, but the onset of digital marketing did prompt new rules and regulations. The one thing that hasn’t changed? Disclosure. Entrepreneur

Facebook Set to Launch ‘Facebook at Work‘ Next Month
Facebook is launching ‚Facebook at Work‘ — a private network for your business — in the next three to four weeks. This will allow inter-office communication in a known format for larger, or even smaller, organizations. The network exists separately from personal profiles so there’s not as much temptation to use personal Facebook during work time. Social Media Today

Content Marketing Takes a Turn for the Better: New 2017 Research
MarketingProfs and Content Marketing Institute paired up to produce their B2B Content Marketing 2017: Benchmarks, Budgets, and Trends — North America, and the findings are fascinating. Most notably, marketers are more positive about their content marketing efforts than they were in the previous year. Read the full report, it’s full of great information. Content Marketing Institute

Snapchat reveals its $130 Spectacles and rebrands as Snap Inc.
Snapchat revealed Spectacles, a $130 pair of sunglasses with a 115-degree lens camera. According to The Next Web, „content recorded using the glasses is automatically pushed to the Memories section of the Snapchat application in a new circular video format — which can be played full screen in any orientation — via Bluetooth or Wi-Fi.“ The Next Web


Salesforce, Google, Microsoft, Verizon are all eyeing up a Twitter bid
TechCrunch reports: Twitter continues to inch its way to a sale process, and the latest developments come in the form of alleged bids from potential buyers … we have also independently heard that both Google and Salesforce are interested in buying the company. We have additionally heard that Microsoft and Verizon have also been knocking.“ What, if anything, could this mean for Twitter marketing? TechCrunch

Google (finally) launches cross-device retargeting
According to Marketing Land, Brad Bender, VP of display and video advertising at Google revealed breaking news about Google AdWords retargeting: „We’re introducing cross-device remarketing for Google Display Network and DoubleClick Bid Manager to help you reach the same user across devices, apps, and sites.“ This means marketers can target the same users, across devices, for a more cohesive experience. MarketingLand

Facebook Allowing Advertisers to Create Rules for Turning Off Ads, Email Alerts
According to recent information submitted to SocialTimes, „Facebook appears to have given advertisers the ability to establish rules to automatically turn off ad or send email alerts once certain criteria are met.“ The report comes complete with screenshots for reference, we’ll have to keep an eye out for this developing story. SocialTimes

What were your top online marketing news stories this week?

I’ll be back next week with more online marketing news! Have something to share? Drop it in a comment or tweet to @Tiffani_Allen or @toprank.

The post Online Marketing News: Bird’s Eye View, FTC Skepticism and Facebook for Business appeared first on Online Marketing Blog – TopRank®.


The Swearing Collection


Mehr Bilder…

The Swearing Collection ist der Titel meiner Diplomarbeit, die ich im Juli 2016 an der Staatlichen Akademie der bildenden Künste präsentiert habe. Ich habe mich dafür ausführlich mit dem Thema “Fluchen” beschäftigt. Dessen Ursprung, Geschichte, Wirkung in unserem Gehirn, Auswirkungen in der Gesellschaft usw. Das Ergebnis ist die Swearing Collection. Eine weiße in leinen gebundene Box deren Inhalt ein Buch, eine Audio Kassette, eine Zeitung, ein T-Shirt und drei im Siebdruck produzierte Poster umfasst. Das Buch “The Swearing Atlas” ist eine Enzyklopädie die alle Gebiete des Fluchens abdeckt und in zwei Teile geteilt ist. Der erste Teil, leitet einen illustrativ durch die Welt des Fluchens, im zweiten Teil, welcher sich durch einen Papierwechsel abtrennt, findet der Leser wissenschaftliche Texte, Interviews und Essays zum Thema. Aus der Recherche für das Buch folgte im Grunde genommen eine Hauptaussage: Fluchen ist gut für den Mensch, in verschiedensten Hinsichten, eine und mit die wichtigste ist, dass Fluchen im menschlichen Gehirn Stress abbaut, diese Aussage war Grund für das “Swearing Tape – Spoken Word and Sound Therapy”. Der Zuhörer wird durch eine geführte Meditation geleitet, welche aus Flüchen in sieben verschiedenen Sprachen besteht, begleitet von Wellenrauschen und entspannenden Gongs. Der dritte Teil der Collection besteht aus dem “Swearing Club”. Ein Verein, der sich für das Fluchen einsetzt. Für den Verein habe ich eine Vereinszeitung “The Swearing Paper” gestaltet und publiziert, welche unter anderem Erfahrungsberichte von Mitgliedern, Fotoserien und das Manifest des Clubs enthält. Jedes Mitglied bekommt selbstverständlich ein “Member Shirt”. Im Siebdruck entstanden, auf fair trade Materialien von Hand mit Liebe und der richtigen Portion an Fluchen gedruckt, bekommt jedes neue Mitglied eines davon ausgehändigt. Eine Posterkampagne darf natürlich für den Verein nicht fehlen. Ich gestaltet drei Postermotive, welche die Vorzüge des Fluchens, wie zB. das Fluchen ein Zeichen für ein perfekt funktionierendes Gehirn ist, illustrativ darstellen. Die komplette Swearing Collection ist in einer limitierten Auflage von zehn Exemplaren entstanden. Die Einzelnen Elemente gibt es in höhere Auflage und bald folgt auch der Swearing Shop und auch der Online Swearing Club ist in Aufbruchstimmung. In diesem Sinne: Start Swearing now to improve the quality of your fucking daily life!

Anna Maria Kiosse