## Excel Formula Tips

These are just some of the more powerful excel formulas I've found useful.

VLOOKUP - a way to look up one value in a different spreadsheet or range on the same spreadsheet and get a value from that other range.

Usage - VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup Value is what you are trying to match

Table_array is the data you are searching

Col_index is the column that contains the data you want, represented by it's column number in the table_array (see example below)

Range_lookup is whether the table_array data is already sorted. I always use FALSE

Example Problem: Spreadsheet 1 has a list of first names, last names, email addresses and usernames in columns A, B, C and D, respectively. In spreadsheet 2 I have a list of email addresses in the A column, some of which overlap the email addresses in the first spreadsheet. I need to figure out the existing usernames for the email addresses that are on the first list.

Example Solution: In spreadsheet 2, create a username column and use the following formula in the top cell "=VLOOKUP(A1,Spreadsheet1!:C:D, 2,FALSE)"

What you'll see is either the username from the first sheet next to the email address or N/A.

Paste Special

A way to take formula results and make them more useful somewhere else

Continuing the example from above I now have a list that is part usernames and part N/A. Now I need to work with this data. To save myself from losing this data, I want to put this information in another spreadsheet where I'll generate usernames for all those N/As. To do so, I copy the columns as usual but instead of using a normal paste, I right click the destination cell and select Paste Special (or select Edit -> Paste Special from the menus) and then choose the Values radio button. This puts a non formula version of my data in my destination cells.