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.
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.
![]() 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:
By double clicking, the formula gets filled into every single value in column B. Below is the VLOOOKUP example used in this excercise ![]()
0 Comments
Leave a Reply. |
AuthorAbe 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. ArchivesCategories
All
|