Hlookup

HLOOKUP

Whenever anyone thinks about a lookup function, the first thought is typically Vlookup. Sometimes however a Hlookup will do the trick. Both functions are virtually identical except that Vlookup searches vertically while the Hlookup - yes you guessed it- searches hortizontally.  ( For my purposes I deliberatley excluded discussion of Index Match for those of you wondering.)

In the example below, I am using the HLOOKUP function to retrieve the Total budgeted expense for whatever month happens to appear in cell C9.

Since I want to retrieve the Total which is at row 7  that becomes my row index number.

My table_array which I called budget_expenses is C1.N7.  You don't really need the table array since we are not copying the formula anywhere- but it is a good habit to get into.


I know that what I am looking for is in the table so FALSE is not really needed - but again it is a good habit to get into particularly if you can't type or spell :)


So, essentially, Excel looks at the table array, and looks across the top row for a match with what is in C9.
In my example, it is January. When it finds a matching column, it then goes down 7 rows and retrieves the value it finds.

1. Select the cell range C1:N7.

2. Name the cell range Budget_Expenses.

3. Click on cell C10 - this is where the answer will display.

4. Enter a HLOOKUP function in cell C10 that returns the Total value. Use Budget_Expenses as the table array and cell C9 as the lookup value.

(Hint: Total is the 7th row).

5. The answer for January should be $31,650. Change B9 to November and watch the total change.

If you use a Data Validation in C9, you will save yourself some time and typing errors down the road.

Go To Special

 Go To Special feature can be very useful in a variety of ways.
Many of the selections are pretty self explanatory. For instance, you can use it to jump to comments in a spreadsheet as well as formulas and errors. If you use the audit features in Excel, you can have it immediately jump  to precedent or dependent cells.

One that you may not see a use for is Visible Cells Only.
If you subtotal data and copy it elsewhere, all the data pastes(even the hidden data).
Okay- see where I am going with this?
If you select your subtotals and then select the Go To Special dialog box and select Visible Cells Only - if you then select Copy- only the visible cells are copied. So, when you Paste, only the visible subtotals are pasted.

Finding Conditional formats and data validations are also useful.

In Excel 2003, press F5 and it will take you to the Go To dialog box and the Special button is at the bottom of the dialog box. You can also find in under the Edit menu.
In Excel 2007, select Find and Select icon on the Home Tab and you will see Go To Special....

Hey, it's Friday. Have a great weekend.

I love to use Data Validation to create dropdown lists - particularly when working with Vlookups but I found it irritating that my source list had to be on the same sheet.  After being extremely lazy I finally got around to looking for a work-around and found one at Contextures.com. 

1. Create your list
2. Range Name it - (Select your data, click in the Name Box and type a name and then press Enter)

In this example, I selected my data and created a range name called product.The Name Box is directly above column A.

• Select a sheet and cell where you want the drop-down list to appear.
• Select the Data tab
• Select Data Validation
  
  Select List from the Allow: drop-down
  In the Source: section, type = product
• Click OK

  

Click on the drop-down and see the list you created.

Table of Contents is not just for Word!

Table of Contents

When you think of Table of Contents, you immediately think of  Microsoft Word but it can also be useful in Excel. I think I am showing my age with this one but I do believe that Excel 4 or Excel 5 allowed you to create a Table of Contents in a workbook. Well, we are up to Excel 7 ...and soon to be 10 if you can believe it and while it is not a feature you can still have a Table of Contents - you just have to create it. It is very easy as you can use hyperlinks.

Why a TOC ?A Table of Contents allows you to easily organize and pull your data together even if it is all over the place. It can be useful for yourself or if you share files with others. It is also a very useful way to navigate around if you are doing a presentation.  Additionally, it is a great way to find files that are linked externally or for that matter hidden worksheets.

In this example, I have a sheet that contains all my input or analysis, another with charts and then a sheet with other data I want to present.

I created a new sheet called TOC and this is where I want to put all my hyperlinks.

To Insert a Hyperlink within the document, click on a cell where you want the hyperlink to display

Click Insert>Hyperlink

In the first column called Link To:
Select Place in this Document

Select the sheet you want to link to by clicking on it and then type the cell reference in. (You need to know the cell you want to link to ahead of time)
In Text to display, type in a name to display in the cell otherwise the default is that it will display the sheet name and cell reference.
Click OK.
Yes, it is that easy. In the Hyperlink dialog box, you can also link to an existing file, existing webpage or even create a new document to link to.

Below is a picture of a TOC that I created.

  Notice that I even have a link to a PowerPoint presentation.

Hyperlinking is quick and efficient.

Just a word of caution, if you hyperlink to files on your computer or network and then do a presentation elsewhere - make sure the links work. Generally, you need have all the files with you if they are not hyperlinked to the Internet.

I hid the gridlines on TOC so that it would look better when I displayed it for an online presentation
Go to the View tab and in the Show/Hide Group, remove the checkmark from gridlines.






Have a great weekend. I am so glad it is Friday!

SUMPRODUCT using Conditionals

As I mentioned the other day, SUMPRODUCT is very powerful. If you looked at the earlier blog, you saw its original or basic use -Multiplying corresponding values in columns and then summing them. If you skipped that blog, you may want to go back and take a look at it first.

With SUMPRODUCT, you can do much more. There is an interesting discussion that explains in great detail how SumProduct works in case you are interested. http://www.xldynamic.com/source/xld.SUMPRODUCT.html
It is so good, that I don't want to repeat it but will just walk through an example.

I can do two different tasks with SUMPRODUCT 
First- Using the example on the left,  I can use it to add up all the Items Sold if the Cost per Item is equal to 2.

=SUMPRODUCT(((B2:B7=2))*(C2:C7))

The resulting answer is 2875. Reminds you of SumIF doesn't it?

Now, for those of you still using Excel 2003, yes- the good news is that you can have multiple conditions.  However, there is a twist to the syntax so it is a bit different.

To add up all the Items Sold if the Cost per Item is equal to 2 or equal to 1 you would use the following formula.
=SUMPRODUCT((B2:B7=2)+(B2:B7=1),C2:C7)

Notice that I am not using an asterisk * but a comma. If you use an asterisk you will get an incorrect answer. So, be careful.

All the conditions are considered part of Array 1.

 

   

 

Second task - Multiplying Corresponding Columns with a Conditional and then Summing

Anyone noticing that all of a sudden SUMPRODUCT is just summing?
What happened to the Product part of the function?

If I wanted to multiply all the Costs and the Units Sold if the Items equaled 2, the equation would look like this:

=SUMPRODUCT((B2:B7=2)*(C2:C7)*(B2:B7))

Interesting eh? Notice that the asterisk is back and that everything is considered part of Array1.

There is a lot to this function so take a look and play around with it.

Two-condition formulas: SUMIFS vs. SUMPRODUCT.(Microsoft Excel 7.0): An article from: Strategic Finance
Cheers!