VLOOKUP Function

VLOOKUP in excel stands for vertical lookup. This simple function is extremely useful if you have large lists of data. With this function, you can easily search through a list of data and match specific criteria. In the example below, we will use this function to match a batting average with a specific player.

Syntax

To start off, lets take a look at the VLOOKUP syntax.

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup _value:
This is the value you want to match. The lookup _value can be a text, a logical value (TRUE or FALSE only), a number, or a cell that references a value.

table_array:

This is the range of data range that our function uses to perform our search. The array used in VLOOKUP must contain at least two columns. The first column of data contains the lookup_values.

* It is best to use an absolute cell reference for the table_array. By using “$” in the range, this keeps a cell reference fixed on a certain cell, or cells.

col_index_num:
Enter the column number of the table_array from which you want data returned from. For example:

• if the col_index_num is 1, it returns a value from the first column in the table_array;
• if the col_index_num is 2, it returns a value from the second column in the table_array.

range_lookup:
True or False. The range_lookup must be a logical value.

• If TRUE or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, VLOOKUP uses the next largest lookup_value.
• If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.
  

HLOOKUP Function

HLOOKUP stands for horizontal lookup. The difference between HLOOKUP and VLOOKUP is that HLOOKUP searches for data in rows instead of columns.

Syntax

To start off, lets take a look at the HLOOKUP syntax.

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

lookup _value:
This is the value you want to match. The lookup _value can be a text, a logical value (TRUE or FALSE only), a number, or a cell that references a value.

table_array:

This is the range of data range that our function uses to perform our search. The array used in HLOOKUP must contain at least two rows. The first row of data contains the lookup_values.

* It is best to use an absolute cell reference for the table_array. By using “$” in the range, this keeps a cell reference fixed on a certain cell, or cells.

row_index_num:
Enter the row number of the table_array from which you want data returned from. For example:

• if the row_index_num is 1, it returns a value from the first row in the table_array;
• if the row_index_num is 2, it returns a value from the second row in the table_array.

range_lookup:
True or False. The range_lookup must be a logical value.

• If TRUE or if this argument is omitted, HLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, HLOOKUP uses the next largest lookup_value.
• If FALSE, HLOOKUP will only use an exact match to the hookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.

HLOOKUP Example

In the image below, we are telling Excel in Cell B1 to look at the range B3:E4 and find to Ty Cobb. Once Ty Cobb is found, we want HLOOKUP to show Ty’s batting average.

SUMIF function

The SUMIF function adds cells specified in range by a certain condition or criteria.

The Syntax for SumIF Function:

=sumif(range,criteria,[sum_range])

Range: A range is a range of cells you want to apply a specific criteria against.

Criteria: The criteria determines which cells to add.

Sum_range: Cells to sum.

Example:

The following example shows how to properly use the SumIf Function. Basically, we are using the SumIf function in Cell D3 to determine how many runs the Tigers scored in Home. The example uses the range A3:A12. The SumIf function matches the word “Home” in the range A3:A12 then adds the runs in the Sum_range B3:B12. Since our criteria is “Home,” the SumIf function ignores all of the away games.

Other Examples:

=SUMIF(A3:A12,C3,B3:B12) —– The SumIf Function returns: 20

=SUMIF(A3:A12,”Home”,B3:B12) —– The SumIf Function returns: 20

=SUMIF(A3:A12,”Away”,B3:B12) —– The SumIf Function returns: 13

Excel text functions – part 2

Now let’s consider a slightly more difficult situation. In the following example we have a description and an amount in the same cell, but the two are always separated by a hyphen:

Because neither the length of the text or the figure are necessarily consistent, we can’t use Left, Right or Mid. However, we can instead use the hyphen to work out where the description ends and the number begins. To do this we must first identify how many characters from the left there are before the hyphen.

To do this we use the ‘Search’ function. Here is the function screen for Search:

Note again that we can just type the hyphen into the ‘Find_text’ box and Excel will automatically add the speech marks. Note also that there is the option to specify the character position at which you want to start the search. This is useful if you need to locate more than one similar character – once you have found the first, you can start the next search from one character position higher. Our example is a simple one that doesn’t use the ‘Start_num’ argument and, as you can see, it returns the position of the hyphen as character 6.

=SEARCH(”-”,A13)

We can now ‘nest’ the Search function within the ‘Left’ function to retrieve the description:

=LEFT(A13,SEARCH(”-”,A13)-1)

In order to exclude the hyphen itself we have subtracted 1 from the result of search. If we copy this formula down our list we can see that it achieves the desired result:

Now to deal with the amount. Whilst we can use Search to find the starting position, we don’t yet know how long the amount is. We can work this out using the ‘Len’ function. ‘Len’ is a very simple function with just one argument – the text string, or cell containing the text string, that we wish to find the length of:

=LEN(A13)

This tells us how long the text is in total, and we have already used Search once to find the position of the hyphen. By combining Len and Search we can calculate how many characters follow the hyphen:

=LEN(A13)-(SEARCH(”-”,A13))

In the case of “Sales-10000” Len will return 11, the hyphen is at position 6, so 11-6 = 5, the number of characters in the amount.

We can use this with the ‘Right’ function to pick out the amount:

=RIGHT(A13,LEN(A13)-(SEARCH(”-”,A13)))

Again we can copy this formula down the list:

As you can see above, whilst we have indeed separated out the amount characters, Excel is still treating our text as text and if we used Sum to total column C we would get zero:

We need to convert the text ‘amounts’ into proper numbers. To do this we use the function Value. We will use the value function to convert the three items in our list to numbers. Here is the formula for cell D13:

=VALUE(C13)

We can now copy this down our list and use Sum again to total our new column:

As you can see, the text values are now treated as numbers and Sum works correctly.

These two functions can be used to remove unwanted characters from text. Sometimes, if you import text from other sources, you may end up with non-printing characters, such as carriage returns – Clean will remove these. Trim can be used to get rid of extraneous spaces:

In the following example we have part of an address that includes multiple spaces between ‘High’ and ‘Street’ and a carriage return character to separate the lines of the address.

In column B we have used Trim to get rid of the extra spaces:

=TRIM(A19)

and then in column C we have used Clean on the result to remove the carriage return:

=CLEAN(B19)

Note that the Trim function leaves a single space between High and Street, but that the Clean function removes the carriage return entirely.

Excel text functions Part 1

This is the first part of a two part article that was prompted by a comparatively simple query about concatenating text. As well as dealing with that query, we’ll look at some of the simpler Excel text functions for working with text. Part 2 will follow shortly and, in it, I’ll look at some slightly more advanced functions for dealing with less predictable text entries.

First of all let’s deal with the actual query, which asked how to combine text in two separate cells into a single cell. There are two principal ways to achieve this. Perhaps the simplest is to use the ‘&’ within an Excel formula:

In our example we have typed three items of text in columns A, B and C. In cell D1 we have entered the following formula to combine all three into a single cell:

=A1 & ” ” & B1 & ” ” & C1

Alternatively, there is an Excel function that concatenates text in this way. Unsurprisingly it is the ‘Concatenate’ function.

In the following screen shot we have used ‘Insert, Function’ to enter the required details. Note that in order to include spaces between the items of text, we have included “ “ between each pair of cells. Using the Insert Function screen, you just need to enter a space in the appropriate text box – Excel will add the speech marks for you:

So far so good, now let’s see how we cope with combining numbers and text. If we just want the number without worrying about the number format, then we can use exactly the same formula as for two items of text. Here we have included some ‘literal’ text in the formula together with a number in a cell:

As you can see the format is not ideal:

In the following example we have used the ‘Text’ function to format the number in the cell:

Note that you can also use named ranges. So if we name cell B3 as ‘profit’ we could write the formula as:

=”Profit is ” & TEXT(profit,”£#,##0″)

Note that we have included a space after the ‘is’ and before the “ so that the number does not follow on immediately from the text.

To see the text functions available in Word, select Insert, Function and then choose the ‘Text’ category (note that the examples shown are from Excel XP, other versions’ screens are slightly different):

As you can see there are lots of text functions, we will look at a few in detail, but if you want to explore all of them, just scroll through the list using the down arrow key. As you select each function in turn you will see a brief description of what it does towards the bottom of the screen. For more details, click on the ‘Help on this function’ link:

This group of functions can be used to return specific sets of characters from a text string. As you would imagine, Left is used to return a certain number of characters from the beginning of a text string, Right is used to return characters from the end and Mid to return characters from anywhere within the text string.

As an example, we will look at some nominal ledger codes. We will assume that the first two characters represent the company, the next three the branch, and the last four the type of expense or income:

First of all we will use the Left function to list the first two characters in the company column. We can either use Insert, Function or just type the function in directly if we know the required syntax:

=LEFT(A7,2)

Now let’s use Right in a similar way to sort out the four characters from the end of the code:

=RIGHT(A7,4)

As you can see, the syntax of Left and Right are very similar, just referring to the cell holding the code and the number of characters. The final function that we will look at in this section is ‘Mid’ and the syntax for this one is slightly more involved because we need not only to specify the number of characters, but also from which character to start. For this reason you may find it easier to use Insert, Function:

This should create the following formula:

If we now copy the three formulae down to the end of our list, we can see how our text string has been split into the three different sections:

Round function

A less drastic method of avoiding rounding errors is to use Excel’s ROUND() function. This allows you to round a number to a specific number of decimal places for use in calculations, without changing the original number. Round takes two arguments – the number to be rounded and the number of decimal places to round to. In this case we have used =ROUND(A2,0) to round to round pounds:

The ’sum’ formula in cell C5 sums the rounded numbers in C2 to C4, so returns 99 rather than 100. If you need the total to be a particular figure, for example if the 100 was a profit share, then you could set the final rounded figure to be the total minus the sum of the other rounded figures. In this case we have entered a figure of 100 in cell E5, then rounded the calculation of the two shares in E2 andE3, with E4 being =E5-SUM(E2:E3)

More fun with rounding

As well as 0 for no decimal places or a positive number for that number of decimal places, you can also enter a negative number as the decimal place argument of the Round function. For example you could enter -3 to round to thousands:

To change the format to display numbers rounded to thousands you would use the following custom format:

#,###,

for 1,235k

#,###,k

for millions

#,###,,

for 1m

#,###,,”m”

The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.

IF function in Excel Part 2

A couple of days ago, I received a question from a reader asking how to use the IF function in Excel for a particular scenario he needed to do at work. I thought other people might benefit from having a short tutorial on this, so I’m providing it here, divided into a few parts.

The first part (here) will go into the basic IF statement, and subsequent parts, such as how to use nested IF functions with other logical operators, will describe how to use it in slightly more complicated scenarios or with additional features. For example, the reader’s question had to do with using an IF statement given two conditions, e.g. if a number falls between two other numbers. I address those in the separate post in the link above.

But, let’s go over the basic IF function here, first.

Let’s say that you have a series of numbers in Excel like this that represent sales figures that 10 of your salesmen have made this quarter, in column B:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A4 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

	Sales
Salesman A	87925
Salesman B	100000
Salesman C	145000
Salesman D	200750
Salesman E	178650
Salesman F	99555
Salesman G	147000
Salesman H	213450
Salesman I	122680
Salesman J	92500

Ok, now back to the tutorial.

Next, suppose that you give them a bonus commission of 12% if they’ve exceeded a threshold value, say, $100K. Rather than going through each sales figure yourself and manually checking to see if that number is greater than $100K, and if so, calculating the commissions in another column (column C in this case), you can use the IF function to do this for you.

First, let’s put the 12% commission percentage in B1 so we can change it later if we want to, and the $100K figure in B2 for the same reason:

Here’s the syntax for the IF statement in Excel. You put the function in the cell or cells where you want the result. The IF function takes three inputs:

=IF(condition to test for, what to do if the condition is true, what to do if the condition is NOT true)

The first two arguments are required, and the last one is optional. If you leave out the part about what to do if the condition isn’t true, then Excel will return “FALSE” automatically. This can be useful in certain situations. In our case, just to avoid confusion, we’ll put in a specific action for what to do when the condition is not true.

So, in our case, we’d start in cell C5, where we want to calculate the salesman’s bonus, if any. The IF statement would be written as.

=IF(B5>$B$2, B5*$B$1, “No bonus”)

Like this:

Here’s what that says in plain English: If the number in cell B4 is greater than the number in cell B2, then multiply B4 by B1. If not, then write “No bonus” in the cell instead. Once you enter in this formula, the result will be printed in the cell. In this case, Salesman A didn’t make enough sales, so “No bonus” appears in the cell.

By the way, the dollar signs by $B$2 and $B$1 are simply an ugly way that Excel uses to keep a cell “fixed”. Later on, we’ll copy and paste the IF function we wrote in cell C4 and the formula will automatically adjust for each row. For example, if we paste the formula in cell C6, the formula will automatically change to:

=IF(B6>$B$2, B6*$B$1, “No bonus”)

If we hadn’t put “$” by B2 and B1, then these would have automatically adjusted as well when we pasted the formula in another cell. Of course, you could avoid all this and put 100,000 and 12% into the IF statement directly, but if you needed to change these parameters later on, it’d be much harder to do.

After copying and pasting the IF formula down the column, here’s our result:

And voilá. It’s done. Now you can format all the numbers and alignments nicely so it’s more readable.

If you have several rows of data, the IF statement is a lot easier a tool to use than going through each case manually! In an upcoming post, I’ll try to describe some other nifty things you can do with the IF statement.