Vlookup - Excel Challenge #2

Many times I've had to create lists that had a species' scientific name next to its common name. 

Early in my undergrad, one of my assignments was to keep a list of all the species I encountered during the semester and make a spreadsheet containing each species' taxonomic information. Back then, I looked up every name one by one and filled in the details manually. Needless to say, it was a tedious process.

Since then, I've had to perform similar tasks but have learnt to be much more efficient. This is largely because of the Vlookup function that I will demonstrate how to use in this post.

The Challenge: 

In the list of bird species below, how can we efficiently add a species' scientific name next to its common name?

Okay, before we begin we'll need a list of all the bird species and their scientific names. Luckily, a quick google search leads to such a list; I used one provided by the American Birding Association. It can be accessed here:  http://listing.aba.org/aba-checklist/

Solution:

1) Copy columns B and C of the ABA checklist into a new worksheet

• To do this efficiently, click cell B2 in the ABA checklist, hold shift, and then press the right arrow.
• Now press CTRL, SHIFT, and the DOWN ARROW simultaneously
• All records of interested should now be highlighted. Press CTRL + C to copy
• Go back to your spreadsheet and create a new worksheet by pressing SHIFT + F11
• Click in cell A1 and press CTRL + V to paste

You should now have this:

Here we can rename the worksheet by right clicking on the tab and selecting rename. Let's rename our original worksheet "LIST" and the new one "ABA". While we are naming, let's also type "Scientific Name" as a header for the LIST worksheet (Cell B1)

Okay, now here is where the magic begins:

2)  In the LIST worksheet, click in cell B2

3) Type =VLOOKUP(

4) Click in cell A2

5) Type a comma ,

6) Click on the ABA tab and select all the cells in the ABA worksheet

7) Type a comma ,

8) Type 2

9) Another comma ,

10) Type false

11) Type closed bracket )

The function should say =VLOOKUP(A2,ABA!A1:B988,2,FALSE)

12) Hit ENTER

13) To lock the cells, add dollar signs =VLOOKUP($A2,ABA!$A$1:$B$988,2,FALSE)

14) In cell B2, there should be a little black square at the bottom left of the cell. Double click it.

This should have retrieved all the scientific names for the given common names

If you drag the black square past your last entry, a #N/A will appear in the cells. However, when a new common name is added to the list, the adjacent cell will now automatically retrieve the scientific name.