back to CATS home

Knowledge Base

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.

 

You must be logged in to save your changes.

Login