Excel Tip: VLOOKUP or HLOOKUP?

Many of us use VLOOKUP regularly and I know I've covered it a number of times on the blog, but what about its poor relation, HLOOKUP?

I regularly come across users who don't know it exists - or if they do, don't know what it is for.

This is a shame, as if you understand VLOOKUP, it is very easy to grasp HLOOKUP and sometimes it is exactly what you need.

In this post I will cover VLOOKUP again for completeness and then describe how HLOOKUP differs.

Put simply, HLOOKUP works exactly the same as VLOOKUP, except that it works horizontally rather than vertically (that's what the V and the H stand for).

Where VLOOKUP enables you to search down a vertical list to return a value from further along that row, HLOOKUP searches along a horizontal list and returns a value from further down that column.

To recap on VLOOKUP:

=VLOOKUP(lookup value,range to lookup,column to return,TRUE or FALSE)

lookup value: This is the value you wish to search the first column of your table for (this can be the actual value, but it can be very powerful to refer to a cell containing the value).

range to lookup: This is the range (either named, or referred to directly) that contains the lookup table. It is useful to refer to whole columns as this allows your list to grow.

column to return: This is given as a number, where the first column (the one being searched) is 1.

TRUE or FALSE: This is a rather odd one. If you enter 'TRUE' or omit this argument, Excel will go to the closest value if the actual value being looked up is not there. This requires the first column to be sorted in ascending order. For most financial applications you will need to enter 'FALSE' here as this will return an #NA error if it cannot find the lookup value. For a fuller explanation of applications where you would use TRUE, take a look at EXCEL TIP: Look up commission or tax rates from earning bands.

HLOOKUP works in exactly the same way, so for HLOOKUP:

=HLOOKUP(lookup value,range to lookup,ROW to return,TRUE or FALSE)

lookup value: This is the value you wish to search the first ROW of your table for (this can be the actual value, but it can be very powerful to refer to a cell containing the value).

range to lookup: This is the range (either named, or referred to directly) that contains the lookup table. It is useful to refer to whole ROWS as this allows your list to grow.

ROW to return: This is given as a number, where the first ROW (the one being searched) is 1.

TRUE or FALSE: This is a rather odd one. If you enter 'TRUE' or omit this argument, Excel will go to the closest value if the actual value being looked up is not there. This requires the first ROW to be sorted in ascending order. For most financial applications you will need to enter 'FALSE' here as this will return an #NA error if it cannot find the lookup value. For a fuller explanation of applications where you would use TRUE, take a look at EXCEL TIP: Look up commission or tax rates from earning bands.

HLOOKUP deals with a situation where your data is in columns and you want to pull data from a column based on the column heading.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: A simple dependent drop-down list

I have previously blogged about how to create a drop-down list in Excel using data validation, but sometimes I am asked to make the options in one drop-down list dependent on the selection from another.

This can be done pretty simply if all you want to do is present different lists based upon the selection from the first drop-down.

For example, we might have a drop-down to choose product group and then a second drop-down to choose an actual product from that group. In the second drop-down, we want to only show the products in the chosen product group.

So here goes...

The first task is to layout the lists in a way that makes this possible:

As you can see above , we have entered the product groups along row 1, with the products contained in each below them.

We then need to name the following ranges on this sheet as follows:

GroupList    A1:C1
ProductList1  A2:A51
ProductList2  B2:B51
ProductList3  C2:C51

I have assumed that we only have 3 product groups and up to 50 products in each, but obviously these can be changed to suit the data.

We then want to create the two drop-downs on a separate sheet - let's say in cells A1 and B1.

The Product Group drop-down in cell A1 is just an ordinary drop-down, where we select Data Validation -> List and enter =GroupList in the Source box (my earlier post explains this in more detail).

The second drop-down is the one that does the work. I will tell you what we do first and then explain it.

In cell B1 (where we want the product drop-down), we again select Data Validation -> List but this time enter the following into the Source box:

=INDIRECT("ProductList"&MATCH(A1,GroupList,0))

we should also untick Ignore blank as there are blank cells in the product ranges we selected. If we leave this ticked, Excel will still present the drop-down list, but allow anything to be typed into the cell.

That's the job done - this will now provide a drop-down of the right products for the chosen product group.

So, what is this formula doing?

There are three elements that we need to understand to decipher the formula:

=INDIRECT() forces Excel to recognise whatever is contained in the brackets as an address or range. We have applied this to the formula so that the Data Validation source box recognises that this is a range, rather than a piece of text.

=MATCH(A1,GroupList,0) searches for the value A1, within the range GroupList. The third argument (0) tells the function that it should only look for an exact match. The result is the position as a number, i.e. if it finds A1 in the first cell of GroupList, it returns 1, etc.

The & symbol combines two pieces of text together into one string.

Putting all three together, if cell A1 was to contain "Product Group B", then the Match formula would return 2 (as this appears in the second cell of GroupList).

So "ProductList"&MATCH(A1,GroupList,0)  =  the text string "ProductList2"

and =INDIRECT("ProductList2") is therefore the range named ProductList2, i.e. the range name we gave to the list of products in Product Group B.

Obviously, the names of the ranges can be changed depending on the data, as long as the dependent list ranges all end with their position.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: Quickly fix formulae copied from another spreadsheet

This week I have a simple tip to solve a very specific problem, but one that many users will encounter regularly.

Many times when building spreadsheets, I have a set of formulae that do what I want in one workbook that I want to copy to a similar workbook with the same worksheet names.

If you've tried to do this, you will know it is not as straightforward as it sounds.

For example, if I have 5 sales reporting spreadsheets in the same format for 5 group companies, I might have an invoice layout in one worksheet (let's call this sheet Invoice) that pulls information from a list of sales invoices on another worksheet (called Data).

If I decide to redesign the layout of all invoices to follow a new format across the group, it would make sense to do this once and copy it to the other 4 when I am happy.

When I copy the new layout and paste it into the Invoice sheet of one of the other companies, all of the formulae automatically link to the Data sheet in the first spreadsheet by adding the file path to the cell references (not what I want).

Where I could approach this with some careful use of Find and Replace, there is a quicker, simpler way that will address the problem for the whole workbook.

Excel stores the names and locations of any spreadsheets that it links to in a table that you can access by clicking "Edit Links" on the Data ribbon (in Excel 2003 or earlier, choose "Links..." from the Edit menu).

Click on the name of the external worksheet in the list of sources (unless you have other links to external spreadsheets in this workbook, it will be the only one there).

Click the "Change Source" button on the right and browse to the location of the new workbook (the one that you are in) and click OK.

The link will disappear from the list as it is no longer an external link - all formulae are now referring to the corresponding location in the new workbook, rather than to an external workbook.

You can check your formulae now and the file paths will have disappeared.

This becomes even more useful when you are copying many formulae in multiple sheets across from one workbook to another - as this process redirects every reference to the original sheet to the new one.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: Simple tips for tidying up text in Excel

Although we might do our best to use data validation to ensure text is correctly entered into a spreadsheet, we very often need to deal with data from other sources that might not be well controlled.

Often one of the biggest issues in dealing with data to make it useable is tidying up text, so that names, departments, addresses, etc.are entered consistently.

Once this tidying up exercise is done, these text fields become useful for filtering the data using autofilter, or pivot tables - or for looking up information from other lists.

A simple approach to take is to work on creating a formula that deals with most of the problems, then copying this down alongside the text list. Then, when we are happy, we can copy this column and paste special (as text) where we want the text to be - if this is a one-off exercise - alternatively we can use the calculated column to drive the reports if it needs to handle new data being added in future.

Excel provides a number of tools that can help to clean up the data.

Removing extra spaces

One of the particularly sticky problems with entered text is extra spaces - particularly because they are so hard to spot. Excel provides a nice simple solution to this, the TRIM function.

=TRIM(text) strips all spaces out of a text string, except single spaces between words.

text can be actual text which must be enclosed in quotation marks (i.e."text") or, as we would use for this purpose, a cell reference containing the text (e.g. A1).

That means that it will remove any spaces at the beginning or end of the text, as well as ensuring that any spaces between words consist of only one space. If they include more than one space, the extra spaces will be removed.

Correcting the case

Particularly where data is to be presented in some way, one big concern can be what case the text is using. This may be all on capitals (rarely looks good on a report), or worse - it might be an inconsistent mix of capitals and lower case.

Excel has three functions to handle this.

=LOWER(text) presents the text entirely in lower case, irrespective of its original case.

=UPPER(text) presents the text entirely in upper case, irrespective of its original case.

=PROPER(text) presents the text in lower case with a capital letter at the start of each word, again irrespective of its original case.

Comparing to valid data

Where the above functions might remove some relatively standard problems, we will often still have to do some editing to completely tidy the list. The time spent doing this can be considerably reduced if we can get Excel to tell us which ones we need to correct.

One of the simplest ways to do this is to use COUNTIF to compare the text to a list of valid options (almost retrospective data validation).

COUNTIF counts the number of instances in a range that meet certain criteria.e.g.

=COUNTIF(Sheet2!$A:$A,B1) counts the number of cells in column A of sheet 2, that contain the value (or text) contained in cell B1.

If column A of Sheet2 contained our list of valid values and column B of the current worksheet contained the text we wanted to correct, then all of the invalid entries will return a 0, which we can then filter the list by to show all of the rows that need correcting.

Happy tidying!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".