VLOOKUP when your data is somewhere other than where you need it…


Recently a friend of mine said the had a marketing list of names but realized somehow they got one with out the phone numbers only emails.  They really needed both.  So after contacting the marketing company now they have two lists one with phone numbers and one with emails.  Well the original list had been scrubbed so some data was missing.  Well it would make it impossible to just add the phone numbers with a quick copy and paste.  This is where VLOOKUP comes in.  I love this function and was introduced to it by a BA while working with multiple lists of defects that needed information consolidated.

So here is how you do it.

With one excel document with the lists in two tabs.  See in this list I have numbers with items and numbers with colors.  So in this example i want to see the colors of the items.  We will assume the number is a unique number of some kind.

        

Add the field that is missing in the list in this case I added Item. Then add the vlookup function to that field.

You should see a form like this.

the value for the first field is the unique field you will be looking for in your other list. So we will highlight the number field and then hit the close button to go back to the function form

then go into the tab with the missing data and highlight the numbers and the column with the missing data.  Go a head and select the entire row.You form should start to look like this.To determine the column index  count the column from left to right that you include in your table array till you get to the column with your data. So in this case it was 2.  The first column tells it where the match is then the index column tells it what data to grab.  Now for the last field I almost always select false because I am looking for exact matches.

click ok.  This will insert the function in the field.  If you did it right you should have your missing data in the field.


Now simply drag the function all the way down through the rest of the feilds.

About dorothyjarry

Super Dots
This entry was posted in Excel. Bookmark the permalink.

5 Responses to VLOOKUP when your data is somewhere other than where you need it…

  1. Hey! Do you use Twitter? I’d like to follow you if that would be okay. I’m definitely enjoying your blog and look forward to new updates.

    Like

  2. Alejandro Martinis says:

    That was a great post. Nice of you to have taken the time to show the steps through the vlookup function here. I’ve seen many people staying clear of it, because they find it complex. This post demystifies that thought completely. Thanks Dorothy.

    Like

    • dorothyjarry says:

      thanks Alejandro. I am trying to help others so it is nice to know a post doesn’t over complicate things and is easy to use. also V-Look ups are a bit confusing. After showing several people I decided just to write a post on it. I am surprised how many people don’t even know what it is considering how much time it can save for just about anyone who uses excel.

      Like

      • Alejandro Martinis says:

        Totally agreed.
        A young student once said: “Nah, I’ll just stick with IF”.
        She had this plan of querying for each value, nesting a new IF every time.

        In case you’re wondering, she finally moved over to VLOOKUP when the data table grew over three lines…

        Like

  3. I’m impressed, I must say. Really rarely do I encounter a weblog that’s each educative and entertaining, and let me tell you, you’ve hit the nail on the head. Your idea is outstanding; the difficulty is something that not sufficient people are speaking intelligently about. I’m very pleased that I stumbled across this in my search for one thing referring to this.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s