DataPedia
  • DataPedia - Home
  • Who we are
  • Business Management System
    • DataPedia - Business Management System
    • BAS
    • Mobile Business
    • Book a demo
  • Excel Consulting
    • Ask Us Any Excel Question
    • Excel Tips and Tricks for beginners
  • Contact Us

VLookup in Excel - The basics...

3/11/2013

0 Comments

 

Our first sheet

Picture
Scenario
So you have a list of names in one column of a workbook - and you want to work out if they are attending a special event that is coming up.

Column A - has the list of names we are looking at (This is our Master List)

Column B - needs to be filled out with either a "Yes" or a "No" using our look-up from another list.

Problem:
The information we want is actually on another Excel sheet - lets take a look at what this 'Other' sheet looks like...

The 'Other' sheet we are looking up...

Picture
Picture
So - if we we were to look at Alex Newman - we can see that he is NOT attending.

What we need to do is retrieve that NO from the 2nd sheet, and put it onto our first sheet for every single name on this list.

This is what the VLOOKUP does in Excel.

How do do the VLOOKUP

Picture
On the master list, click into cell B2 and type in =VLOOKUP(



What this has done is open up an Excel calculation where you can now enter what it is that you want to lookup.

The VLOOKUP formula is asking for these things:
1) WHAT CELL DO YOU WANT TO LOOK UP
2) IN WHICH SHEET DO YOU WANT TO LOOK FOR IT
3) WHEN EXCEL FINDS YOUR VALUE, WHAT DO YOU WANT EXCEL TO RETURN
4) DO YOU WANT AN EXACT MATCH OR JUST AN APPROXIMATE?


Picture
STEP 1 - what we want to lookup is the name in column A of this sheet.
So - simply click on the value in A2 as shown in the picture on the left.

Add a comma after A2 so the formula now reads: =VLOOKUP(A2,

Step 2 is the trickiest.  We are now looking for the name Delbert Strickland on another sheet.

So first - click on the other sheet (again notice how the formula at the top begins to update itself)
Picture
Then, click on ALL columns in between the name (Column A) all the way to the attendance (Column D).  This effectively selects ALL values in these 4 columns between A to D.
Picture
Put a comma after selecting the columns.  The formula should now look like this:

=VLOOKUP(A2,'Other List'!$A:$D,

Now that you have selected where the value is - we need to specify which value we want returned from these columns.

  • If you would like to return the age in column B - put a 2 (which means give me back Column 2)
  • If you would like to return the sex in column C - put a 3 (which means give me back Column 3)
  • If you would like to return the attendance in column D - put a 4 (which means give me back Column 4)
  • If you would like to return the value in column E - put a 5 (which return an error) - WHY? Because we didn't ask the formula to look for anything outside column A to D.

Formula should now look like this:
=VLOOKUP(A2,'Other List'!$A:$D,4,

Finally - put a FALSE after the comma and close the bracket.  

=VLOOKUP(A2,'Other List'!$A:$D,4,FALSE)

Press ENTER to see what happens
Picture
Notice how the value NO is returned for Delbert Strickland.

This was the value in Column 4 of the other sheet.

Now we need to FILL this formula right down to the bottom.

Click into cell B2 (this is the cell we want to fill)

Picture
Notice the black square on the bottom right hand corner of the cell after clicking into it? This is the fill down tool.

  1. Click and hold this black square
  2. Drag it down to the next few cells to fill the formula

Picture
When you drag that  black square down, notice how the values get filled in automatically for us?

To fill every single value in column B - there are 2 ways:

  1. Keep filling down using that black square (in the method we just done)
  2. Double click on the black square really fast


By double clicking, the formula gets filled into every single value in column B.

Below is the VLOOOKUP example used in this excercise
VLOOKUP_Example.xlsx
File Size: 15 kb
File Type: xlsx
Download File

0 Comments



Leave a Reply.

    Author

    Abe Dahbache has been working with EXCEL for over 15 years. He is a Microsoft Office User Specialist and has been developing solutions in Excel for some of the worlds largest companies.

    Archives

    December 2015
    November 2013

    Categories

    All
    Basic Vlookup
    Cleanse
    Concatenate
    Data Cleanse
    Data Strip
    Excel
    Lookup
    Reference
    Values
    Vlookup

    RSS Feed

Powered by Create your own unique website with customizable templates.
  • DataPedia - Home
  • Who we are
  • Business Management System
    • DataPedia - Business Management System
    • BAS
    • Mobile Business
    • Book a demo
  • Excel Consulting
    • Ask Us Any Excel Question
    • Excel Tips and Tricks for beginners
  • Contact Us