Excel tips and tricks

I discovered how to use VLOOKUP. What fun! It was very handy in gathering data across sheets in my document. There are a lot of explanations on the web, but here’s mine for Microsoft 2012. (Most of this information is from Excel’s Help Center--it’s quite useful!)

In Excel 2012, the VLOOKUP function looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP can find a value in the same row as the value for which you want to search. Lets use an example: say you have one spreadsheet you have a list of zip codes and cities name “Cities” and another one that has a list of zip codes and counties named “Counties”, and you want to match up cities with counties in the first spreadsheet. This is how you’d do that:

A visual explanation of VLOOKUPA visual explanation of VLOOKUP

VLOOKUP searches for the specified lookup_value in the first column of the specified table_array and displays a value from a user-specified column in the same row as the lookup_value. The lookup_value can be a value, a cell, a reference, or a text string. If you are inputting a string of text, make sure to enclose everything in single quotations (e.g., ‘hello world’). (The cells you are searching through must be in the same format; for example, if in one place the number is formatted as a number, but in another place it is formatted as text, VLOOKUP won’t be able to find what you want.) In our example, suppose you are looking for the county in which Berkeley is located. The function would look like =VLOOKUP(94707,...) so far.

The table_array is all of the cells you want to search (e.g. A:B if you want to search columns A and B). You can select a table array in a different spreadsheet than the one in which you have the function by either just selecting or, if you don’t want to move your cursor, typing in name of spreadsheet!cells desired. If you want to search in an entirely different document (whoa fancy!), you can select or type in ‘[name of document]name of spreadsheet’!cells. The quotes are very important! In our example, the function now looks like =VLOOKUP(94707,Counties!A:B).

The col_index_num is the column in which the value associated with the lookup_value that you want displayed. The columns in the table_array are numbered 1, 2, 3, etc. starting from the leftmost column. If you have a table with one column labeled “Zip codes”, a second column labeled “City”, and a third column labeled “County”, and your lookup_value is a zip code and the value you want matched is the county in which the zip code is located, the col_index_num will be 3, for the third column. Our function now: =VLOOKUP(94707,Counties!A:B,2) if zip codes are in the first column and counties are in the second.

The range_lookup determines whether Excel will search for an exact or an approximate match. Entering TRUE or omitting a value means that Excel will search for approximate matches. If an exact match is not found, the next largest value less than the lookup_value will be returned. If you enter FALSE, the function will look for an exact match. Our final function: =VLOOKUP(94707,Counties!A:B,2,FALSE) since we want exact matches. The function would display Alameda because that’s our county.

The V stands for “Vertical”. Easy to remember!

HLOOKUP is just the same, but instead of searching columns, it searches rows and displays values in the same column (hence H for “Horizontal”).

I also discovered how to convert PDFs into Excel format! What a lifesaver! That seriously saved me hours and hours of copying and pasting. The method I found required Adobe Acrobat (not Reader!). All you have to do is open the PDF in Acrobat, then Save As an Excel workbook. It’s that easy! The PDF should already be in a table format. I don’t know what might happen if it was just a random PDF...

Microsoft Office Help