Using TRUE in VLOOKUP

Posted on September 21, 2014 by Dan

I've used the VLOOKUP function since the cows last came home. I can't imagine my Excel life without it. It's sublime, and while a relatively simple function, it caters for so many needs. (More on the VLOOKUP function here.)

It takes on the following form:

=VLOOKUP (needle, haystack, column reference, TRUE/FALSE)

The needle is the thing you're looking for. The haystack is the range in which you're looking for it. The column reference is the column number that you want to return. And the TRUE/FALSE at the end determines whether to only find exact matches (FALSE, or 0); or look for inexact matches (TRUE, or 1).

Below is Microsoft's description of this last variable:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

To this day, in 20+ years of using the software, I've never set this last variable to TRUE. Not once. For some reason, I've always been nervous about inexact matches, and the need to find near misses has never arisen for me.

Or it hadn't, until last week.

My friend Steve emailed me. He had a spreadsheet that told him which person had bought which ranges of raffle tickets. (So there were three columns: name, lowest numbered ticket bought, highest numbered ticket bought.) He needed the ability to type in a number of a drawn raffle ticket, and for it to pop up with the name of the winning ticket holder.

At first, it stumped me. But a couple of minutes in, I realised that the TRUE setting would be perfect here. All I had to do was put the people's names to the right of the range, and I could look up the winning ticket number against the column containing the lowest numbered ticket bought, and hey presto!

If Joe has bought tickets 1 through 100, then by setting the variable to TRUE, a lookup of the number 86 will bring back Joe's row, as 1 will be the next lowest number it finds. (Setting it to FALSE would bring back #N/A, as there wouldn't be an exact match.)

It's lovely when something that initially troubles your mind can be solved so simply.

MODEIF and MEDIANIF (conditional mode and median)

Posted on November 13, 2013 by Dan

There's not an inbuilt function to cater for the conditional mode or median. So we need to use an array function.

=MODE(IF($A:$A=D1,$B:$B))

If you have supplier names in column A and their delivery lead times in column B, the above formula finds out what the mode of those delivery times is for the supplier referenced in cell D1. Change MODE to MEDIAN with obvious results.

Because the formula itself references an array, you'll need to hit CTRL+SHIFT+ENTER instead of just ENTER when you've finished writing the formula. Excel will put some lovely curly brackets around the formula for you, reminding itself that arrays are contained therein.

Happy days.

INDIRECT

Posted on May 15, 2013 by Dan

Oh what a gem of a formula this is.

In essence, it allows you to specify the text that you would like to be used to bring back information. So =INDIRECT("A1") will bring back the contents of cell A1. And =INDIRECT("Sheet3!A2") will bring back the contents of cell A2 from Sheet3.

But its power and sheer elegance comes when you string together chunks of references to create something more powerful. In today's example, each sheet was named after a town. A summary sheet contained those towns' names. And we wanted to bring back cell B3 from that town's sheet.

=INDIRECT("'"&A1&"'!"&"B3")

The quotes are a bit difficult to read there. But you've got a double quote, a single quote then a double quote. This basically gives you a single quote in the cell reference. (If all of the sheet names were a single word, then you wouldn't need the quotes, as Excel doesn't need quotes to refer to such sheets. But better to be safe.) After the sheet name reference, you have another single quote and an exclamation mark. So Lyme Regis would yield 'Lyme Regis'! (including the quotes).

That's exactly what you need in a sheet reference. String it together with the B3 using the abbreviated CONCATENATE function (the ampersand (&) does the same as CONCATENATE but using less keystrokes), and you get 'Lyme Regis'!B3

So it would bring back cell B3 from the Lyme Regis sheet.

I used it similarly in a truly hideous spreadsheet I received recently that contained a sheet for each day of the project, each named in DD.MM.YY format. After shooting its author, I used some INDIRECT functions to stack the data into a single consolidated view.

Summary

It allows you to refer to a cell by building up its name.

Syntax

=INDIRECT(Reference,Logical)

Reference above is the text equivalent of the cell reference.

Logical is a true/false or 1/0 value. If you leave it blank or set it to zero, it will expect a traditional A1-style reference (i.e. column letter followed by row number). If you set it to 1, then it will expect an R1C1-style reference, which can be useful if you want to count columns rather than naming them based on their letters.

Highlighting today’s date: a lovely little trick

Posted on April 1, 2013 by Dan

I came up with a neat trick the other day. With each column indicating a sequential date, it was a way of highlighting the current date.

There are two ways of doing this. You can either use conditional formatting to format the entire column; or you can put a cute little down arrow above the date at the top of the column.

First, some principles. Let's assume our dates run across row 2, starting in column B and running across to the right.

Highlighting the column

Highlight all of the columns that house dates: column B all the way over to the right-hand side. And hit Conditional Formatting on the Home ribbon. Hit New Rule, and choose Use a formula to determine which cells to format.

In the formula box, type the following:

=B$2=today()

Now choose the format that you want (yellow fill, possibly), and hit OK.

This compares the date in the column in row 2 with today's date. If they're the same as one another, it will highlight the column. Otherwise, it won't. The dollar before the "2" serves to always look at the value in row 2, irrespective of the location of the cell you're highlighting.

Having a pointer arrow

With cell B1 selected (immediately above the first date), hit the Symbol button on the Insert ribbon. Change the font to Wingdings and double-click the down-arrow symbol towards the bottom of the array of options.

Change the font colour to white so that it's invisible (assuming your backdrop colour is white), and copy that all the way across row 1, above your dates.

Now highlight all of those cells B1 across to its right, and select Conditional Formatting from the Home ribbon.

Again, Hit New Rule, and choose Use a formula to determine which cells to format.

In the formula box, type the following:

=B$2=today()

And then choose a red font colour when the conditional format is true.

Every time you open the spreadsheet, the arrow will have moved above today's date.

All rather neat, don't you think?

 

Protection in Excel

Posted on June 11, 2012 by Dan

Protection in Excel is a bizarre beast indeed. Here's a quick introduction into their intricacies and oddities.

First of all, the essentials:

  • Protection is applied at one of three levels: workbook, sheet or cell
  • Each level works differently.

Let's take it top-down.

Workbook-level

Workbook-level security can be found on the Review ribbon. (In this post, we won't get into the Information Rights Management Service, which is a different bag altogether.) Hit the Protect Workbook button and you're invited to Restrict Editing, with an option titled Protect Structure and Windows.

Protect Workbook

Don't worry about the Restrict Permissions options, as these get you into that world of Information Rights Management.

Then you're presented with two tick-boxes: one for Structure and one for Windows.

Protect Structure and Windows

The Structure option will prevent people from adding or deleting worksheets, or unhiding hidden ones. The Windows option will lock the size and position that is displayed for each worksheet. So you won't be able to scroll around looking for other stuff.

If you don't type a password, then users will be able to unlock the workbook in the same way as you originally locked it. If you do, then they'll need that password to unlock it. Pretty simple really.

Worksheet-level

As its name suggests, this applies to the worksheet only, and can be found immediately to the left of the Protect Worksheet button. It allows you to prevent users from doing certain things within the sheet: format things, insert things, delete things, sort, filter etc.

Protect Sheet

Once again, you have the option of adding a password to the protection.

In here, you'll see a couple of references to "Locked Cells". The tick-box at the top of the dialog box defaults to being ticked, which is fortunate, because this is the option you want 99% of the time. You want to prevent people from doing stuff to cells that are locked.

And the section below details how indeed you format cells to be locked.

Cell-level

Somewhat counter-intuitively, all cells within an Excel spreadsheet default to being "Locked". But because the worksheet isn't protected, this status has no bearing.

If you create a brand new blank workbook and do nothing but protect Sheet1, the contents of all cells will be locked because the cells themselves are locked.

However often the business requirement is for some cells to be editable (usually those that are designed to house values) and for some to be locked (usually those that contain formulae).

To do this, you first need to unlock those cells that you want to be editable, and then protect the worksheet.

Whether or not a cell is locked is a feature found within the Format Cells dialog. So to unlock some cells, select them all (you can select non-contiguous ranges by selecting with the CTRL button held down) and then bring up the Format Cells dialog. To do this, either:

  • Click the expansion button to the right of the Number section on the Home ribbon; or
  • Hit CTRL+1.

Either will bring up the Format Cells dialog.

Hit the Protection tab and you're presented with two options:

  • Locked: This will lock the cells. Its default is to be checked, so to make cells editable, you uncheck it.
  • Hidden: This purely indicates whether the formula in a cell will be hidden. It can be useful if you don't want people to see the ugliness (or elegance) of a formula, and want them just to focus on the value it yields.

Protection

As the comment says, changing the settings here has absolutely no bearing until you protect the worksheet.

So to restrict editing to certain cells within a worksheet, you should undertake the following steps.

  • Select those cells/columns/rows.
  • Select Format Cells and uncheck the Locked tick-box on the Protection tab.
  • Lock the worksheet.

But more often than not, there are certain areas of a worksheet that you *don't* want people to edit. In this case:

  • Select the entire sheet (clicking to the left of the header of column A, immediately above the title of row 1
  • Select Format Cells and uncheck the Locked tick-box on the Protection tab.
  • Select the cells that you don't want people messing with
  • Select Format Cells and *check* the Locked tick-box on the Protection tab.
  • Lock the worksheet.

Excel dice shaker

Posted on May 21, 2012 by Dan

Here's the spreadsheet you've all been waiting for. The Excel dice shaker. It caters for up to four dice. Simply hit F9 to make 'em shake.

Click here to download.

Freeze Panes vs. Window Split

Posted on March 20, 2012 by Dan

Often in Excel, you'll want columns or rows to continue to appear when you scroll off to the right or down the page.

You may have a table of employees with names down the left-hand side, with data pertaining the individuals disappearing off the right-hand side of the screen. Scrolling to the right, you'll want the names to continue to show when you hunt across for their NI numbers, stock plans, start dates.

And when you scroll down the page to get to the individual you're interested in, you'll want the column titles to continue to appear, so that you know which column you're looking in.

There are two ways to do this: the right way and the wrong way.

First: the wrong way. A feature has existed in Excel since I can remember called Split. It used to be on the Window menu, and now sits on the View ribbon. As its name suggests, it splits the window. You can either split the screen into two sections (a top and a bottom; or a left and a right); or you can split it into four.

The trouble with this method is that all of the panes are still navigable. So you can scroll up or down, left or right in any of the two or four panes that appear.

Just try it. Try splitting the screen and then do some scrolling in each of the resulting panes. Confused? You betcha.

Another minor annoyance with this technique is that the bars that separate the panes are distressingly ugly.

The Split function does have its uses—it can be useful to show multiple areas of data at the same time, to allow data entry that relies on other data in the worksheet. But more often than not, it's not the right tool.

Now: the right way.

The right way is through Freeze Panes. It can be found right next to the Split function. The main difference between this and the Split function is that with Freeze Panes, you can only navigate in the bottom-right pane. The top section remains frozen, as does the left section. This reduced functionality is a blessing. Trust me on this.

Oh, and the dividers are much more pleasing to the eye.

With either method, you invoke the dividers by navigating to the cell immediately below and immediately to the right of where you want them to appear. With Split, you also have the option of dragging a little divider immediately above the vertical scroll bar, and a similar one immediately to the right of the horizontal one.

VLOOKUP and HLOOKUP

Posted on March 12, 2012 by Dan

VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data.

But we shouldn't forget about its lesser-used sibling, HLOOKUP.

First, VLOOKUP. For those not in the know, it allows you to look for a value in a range, and bring back a value associated with that value from the range in which you're looking. So you may want to have a cell in which you select an employee's name from a dropdown and for their salary to appear in the next cell. Or you may want to bring back the stock levels for a range of stock IDs.

Below is the syntax of the VLOOKUP function:

=VLOOKUP(cell,range,number,boolean)

The cell is the thing that you want to look up. The needle, if you will. The range is the area in which you want to hunt for it. The haystack if you will. The number is the column number in the target range that you want to return. And the boolean is an indicator to tell Excel whether to only find exact matches (0) or to also return close matches (1). (In my 16 years of using Excel, I've never once set this value to 1.)

The main two rules to understand are:

  • The thing being looked up must appear in the first column of the range in which you're looking it up
  • The number is the column number containing the desired data. So if your range runs from column B to column G, setting this to 3 will bring back data in column D (column B is 1, column C is 2, column D is 3). It must be positive, so the data you return must be to the right of the column you're looking up in.

So take the following formula:

=VLOOKUP(A1,$G:$Z,4,0)

This will take the value in cell A1, look for it in column G (the first column of the range), and bring back the associated data item from column J (the fourth column of the range). If A1 appears more than once in column G, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.

The range doesn't have to be a full set of columns, by the way. It can instead be a square range.

So now to the HLOOKUP.

It's exactly the same, apart from rows and columns being switched.

=HLOOKUP(A1,$7:$26,4,0)

This will take the value in cell A1, look for it in row 7 (the first row of the range), and bring back the associated data item from row 10 (the fourth row of the range). If A1 appears more than once in row 7, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.

I find I rarely use the HLOOKUP function. But it's comforting to know it's there if I might need it. Which I did today.

My friend Will had a bunch of columns representing sequential dates, each row representing a milestone. There would be a single X appearing where those milestones were to hit. He wanted that milestone date appearing as a column next to the milestone name.

The solution involved replicating the date headings at the bottom of the range, given the rule stipulating that the data you need to return must be below the row you're looking up in. We put it in row 100.

And the solution was to look up the value X in the row in which the formula was appearing, and bring back the equivalent value in that newly-created row 100. So in row 5, we put the following formula:

=HLOOKUP("X",5:$100,101-row(A5),0)

Let's break that down.

  • X. This is the thing that is being looked up.
  • 5:$100. This may look odd. It's a reference to rows 5 to 100. The $ against the 100 will keep it fixed when the formula is copied down to subsequent rows. But by not having a $ against the 5, it will float. So copying that same formula down to row 6 will make it change to 6:$100.
  • 101-row(A5). This takes the current row number away from 101. So in this case, yields 96. You want to return the 96th row of data (in this case row 100). By making this a variable, it changes when the formula is copied to the other rows in the spreadsheet. Row 99, for example, will want to bring back the second row's data (i.e. still that in row 100)

So there you have it. VLOOKUP and its lesser-known sibling, HLOOKUP.

COUNTIFS and SUMIFS

Posted on March 8, 2012 by Dan

Yesterday, my friend Alan posed a problem. He wanted to count the number of rows that met criteria in two separate columns. He was frustrated that COUNTIF wouldn't allow him to do that.

Since Excel 2007, there have been a couple of functions that have allowed you to check against multiple criteria being satisfied: COUNTIFS and SUMIFS.

COUNTIFS

COUNTIFS is exactly the same as COUNTIF, except that instead of checking against one range, it checks against many. All of the conditions must be true for the entry to be counted.

For example, in a list of employees you may want to count all people in the New York office earning over $50,000. Or in a risks and issues register, you may want to count all risks with a high likelihood and high impact.

The syntax is as follows:

=COUNTIFS(Range1,Criterion1,Range2,Criterion2,…)

So you basically list the ranges contain the things that you want to check, each followed by the condition that you want to check for. And as long as the areas match up, you're golden.

=COUNTIFS(A:A,"New York",B:B,">=50000")

This will count all rows in which column A contains the value "New York" and column B contains a value in excess of 50,000. Note that any check other than an exact match must be encased in double quotes.

SUMIFS

This behaves in much the same way. And the syntax is only subtly different.

=SUMIFS(SumRange,Range1,Criterion1,Range2,Criterion2,…)

You merely plonk your sum range in front of your list of ranges and criteria.

=SUMIFS(C:C,A:A,"New York",B:B,">=50000")

If stock options are housed in column C, then the above formula will total the stock options for all New York employees earning over $50,000.

Conditional Formatting: How to

Posted on February 28, 2012 by Dan

First: what is Conditional Formatting?

Conditional Formatting is a feature that allows you to change how a cell looks based on the contents of your workbook. You may have a cube of random numbers, and want to shade everything over 0.8. Or you may have a list of employees, and want to embolden those that earn more than £50,000.

Conditional Formatting has been made rather daunting since the launch of Excel 2007.

In the days before 2007, there was a single Conditional Formatting menu option, from which you could do whatever you needed.

The newer version comes with Data Bars, Color Scales and Icon Sets. There's lots of fun to be had in those areas. But 99 times out of a hundred, what we really want is to define the rules ourselves.

And this means that the bottom three menu items are all we need:

  • New Rule…
  • Clear Rules
  • Manage Rules…

If you know how to use the New Rule feature, then the other two become straightforward. So let's dive in. First, highlight the area that you want to be formatted. And on the Home ribbon, hit the Conditional Formatting button in the Styles area.

Again, the pop-up is littered with stuff that you're unlikely to use. Almost always, I resort to one of two menu items here:

  • Format only cells that contain
  • Use a formula to determine which cells to format.

The first of these options allows you to format each cell based on its contents. The latter allows you to format each cell based on the contents of others.

Format only cells that contain

This one's relatively straightforward.

The first dropdown allows you to select attributes of the cell that you want to base your formatting on.

More often than not, you'll choose "Cell Value" here. The Date option can be useful, but otherwise I rarely venture beyond the default.

Once selected, the second dropdown is self explanatory. You select the conditions that need to be met for the formatting to kick in.

Then you select the formats that you want to show when the conditions are met (a richer set than was included in earlier versions of Excel) and you're done.

You can add extra Conditional Formats to allow different scenarios to show different results. Or to allow different cells to be formatted in a multitude of ways. You may, for example, colour numbers under 0.2 green, and those over 0.8 red.

Use a formula to determine which cells to format

This one uses the basics of the above, but takes it to the next level. The key here is that you can format cells based on the contents of other cells. So you can shade an entire row based on the associated value in column K. Or you can insert an upper border if the reference number of the row is different from that in the row above.

(This latter example is useful in visually separating groups of rows. So let's do it.

Let's say I have a sorted list of unique reference numbers with an associated list of fruit.

And I want to insert a rule (or a line, to those of more modest age) in between the 1s and the 2s; the 2s and the 3s and so on. But I want the rule to go across the entire row, not just in between the cells of column A.

To do this, I first highlight rows 2 through 12. Note that the uppermost and leftmost cell highlighted is cell A2. This will appear in the little box above the column A title, just beneath the Clipboard area of the Home ribbon. This is important to note.

Just as before, select the New Rule option from the Conditional Formatting option. But this time, select "Use a formula to determine which cells to format.

Here, you are being invited to type a formula whose answer will be either TRUE or FALSE. If TRUE, then the format will kick in. Otherwise, it won't. It's as simple as that.

In the Formula bar, type the following:

=$A2<>$A1

The formula that you type should relate to that uppermost, leftmost cell, in this case A2. But you should use dollars to ensure that the formula is relevant to the other cells that are being given the formats.

In the above formula, we're comparing the value of A2 with the value of the cell directly above it. If they are different (i.e. the reference number has incremented from that in the previous row), then the formula is true, and the conditional format will kick in. Otherwise, it won't.

A dollar has been placed before each of the references to column A to ensure that the conditions that refer to columns B, C, D etc. still refer back to the values in column A. That is, the only things that are ever being compared are the reference numbers. Dollars have not been placed before the row numbers in the formula to ensure that the row references float. So the formatting in row 8 will be based on a comparison between the reference in row 8 and that in row 7, not those in rows 2 and 1.

And lastly, choose your format. In this instance, we choose an upper border as the format of choice. This puts a rule in between rows 1 and 2; 3 and 4; 6 and 7; 7 and 8; 9 and 10.

Give it a try. It really is rather lovely.