VLookup in Excel - The basics...

Our first sheet

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...

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

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?

---

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)

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.

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

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)

---

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

---

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

---