The Concatenate function is a function that I use almost every day. There are numerous times where you will have data split into many columns but need to look up a value that is unique to the combination of two (or more) of those columns. That is where the concatenate formula comes in handy.

Let’s start by taking a look at the formula:

Concatenate(text1, text2, …)

text1, text2 can be either strings, numbers or cell references that you would like joined together. You can join as many strings into 1 string as you would like.

Example 1: =concatenate(”First”, “Last”) will return “FirstLast”
Example 2: =concatenate(”First,” “,”Middle”,” “,”Last) will return “First Middle Last”

Read more »

Some of the more common formulas I use on a regular basis are date manuputation formulas. Whether it using the Date() formula or the Month() formula or many others, there are several ways to show data that have dates involved.

Let’s start by taking a look at the Date Formula itself:

Date(Year, Month, Day)

where Year, Month and Day are numeric value or cell references to numeric values. The result returns a serial number for the result; if cell was formatted to general before the formula was entered it will be automatically re-formatted in date format.

Example 1: Date(2007, 1, 5) will return 1/5/2007.
Example 2: Date(2007, 14, 5) will return 2/5/2008.
(Note: if the number for the month is greater than 12 year will be incremented accordingly and the months mod 12 will be returned for the resulting months. The same is true if the days entered are greater than the number of days for that particular month)

Read more »

I thought a good next post would to add how to use HLookup after posting the VLookup information last time. The two functions are very similar only they do the opposite things with it comes to looking a against a table.

Let’s start by taking a look at the formula for HLookup:

HLookup(lookup_value, table_array, row_index_num, range_lookup)

lookup_value is what you are searching for in the first column of the table_array

table_array is the range you would like to look up a value for. You must include both the column you are looking for a value in and the column of the value you would like to return

row_index_num is the row number in table_array from which the matching value is returned if a match is found. The first column in your table_array range is 1 (Must be positive integer greater than or equal to 1).

Read more »

The Vlookup Function is a part of Excel that I personally use everyday. So many Excel users have no idea it even exists, so I thought this would be a great place to start for ExcelHints.com. Once you learn how to use vlookup, it will greatly ease many of the pains you have had in trying to group two pieces of information together.

VLookup stands for Vertical Lookup. Basically what it does it looks up a value you tell it to, against another range of cells. If it finds a match, it returns the value in the same row as the match of the column you specify. You can either have it return results for exact matches or the closest match.

Let’s start by taking a look at the formula for VLookup:

VLookup(value, table_array, index_number, not_exact_match)

value is what you are searching for in the first column of the table_array

table_array is the range you would like to look up a value for. You must include both the column you are looking for a value in and the column of the value you would like to return

Read more »

A few new options are now available on the menu for ExcelHints.com. I added a button to show some of the keyboard shortcuts I use regularly to speed up some of the more common actions that can be applied in Excel. The left side of the table is the key combination for the shortcut and the right side of the table is the action that is taken from your key combination.

Excel Keyboard Shortcuts

I also added a button on the menu for Excel Tips. While I am making daily additions to the blog with a “How-To” of the features of Excel I have found valuable, I will also make an index of these features available here. They will be grouped into different catagories (Sort, Macros, Formatting, etc) to easily access the information you are searching for. Clicking on each link will take you to a walk through of how to use that particular feature.

Tips for Excel

I am continuing to work on getting the website up and running. I just completed a contact form, allowing you to contact me with any Excel questions you have, which can be found at Contact Excel Hints. I have also added a Message Board, but I don’t have much experience hosting a board so that will most likely be Trial and Error until we get it right.

In the next few weeks I will be adding many other new features that I believe will increase the helpfulness of the site, including an index of the blog posting to sort through, site search, and a list of common features that will make your Excel Experience that much more enjoyable (maybe enjoyable isn’t the word for everyone, at least hopefully easier).