Vlookup – why do I need TRUE or FALSE?

In the last few days there has been a discussion about VLookup and how the last parameter works when it is entered as TRUE, FALSE or omitted altogether.

The Microsoft Help page was showing an example screenshot, where the formula and the result did not match up.

This was an oversight in the help documentation and will be fixed.

But it sparked a few questions, like:

  • Why do I get an error for this formula, but when I expand the lookup table by one row I get the correct result?

With the last parameter set to 0 or FALSE, Vlookup will go through the list from the top, one cell after the other, and will compare each value in the first column to the lookup value. The list does not need to be sorted. When it finds a match, it returns the value from the specified column. For large lists, this can be rather slow.

Vlookup with the 1 or TRUE as the last parameter (omitting the parameter defaults to TRUE), works differently. Vlookup will now assume/expect that the list is sorted ascending in the first column. In order to find the lookup value, it does not inspect each cell, but cuts the list in half. If the value at the half point is a match — Bingo! If not, it compares the value at the half point with the lookup value and decides: If the lookup value is greater than the value of the half point, then continue with toe lower half of the list. Cut the lower half of the list in half and see. If the lookup value is smaller than the value of the half point, then continue with the upper half of the list. Cut the upper part of the list in half and see.

So Vlookup will start with 1/2 of the list,  then reduce to 1/4 of the list, then to  1/8,  then 1/16, etc. until it finds the match. If it gets down to only one value, but that value is not an exact match, this value will be treated as the match and the corresponding column value will be returned.

Are your ears smoking yet? Let’s look at an example.

Consider this table

The formula in A12 is =VLOOKUP(“h”,A2:B10,2).

Vlookup will split the table in half and will look at the last value of the top half. If there is an uneven number of rows, it will grab the next row, too. So Vlookup will consider this range:

The table has 9 rows, so Vlookup looks at the fifth row of the data table.

With text values, “greater” and “smaller” refer to the position in an alphabetic sort. So “a” is smaller than “c” and “k” is greater than “c”.

The value “i” is greater than the search value “h”. Therefore, the desired value is above Excel row 6. Vlookup will then split this upper part of the table in half. Since there are 5 rows involved, the split will be done at the third row of the range:

Vlookup looks at Excel cell A4. This value “e” is smaller than the lookup value. That means that the value we want is not in the table range that was just inspectedd, so we can forget that part of the table. Next Vlookup looks at the lower half of the range from the previous step.

Again, the last value in that range is “i” and greater than the lookup value, so let’s split this range in half and look at the last row of the top half:

This range consists of one row only. There’s nothing more to split. The value is not an exact match, but the return column will be returned, anyway.

Vlookup with TRUE as the last parameter will deliver the value that is “equal to or smaller than” the lookup value.

Coming back to the example with the names at the top of the post:

The formula used is =VLOOKUP(“Akers”,B2:D5,2)

The range is split in half, the last value in the top half is greater than the lookup value, so the lower half of the table will not even be considered for further inspection. The top two rows will be split in half and a value smaller than “Carido” will be expected in the last cell of that next split. But the last value in a split has “Weiler” as the value. That is not smaller than the lookup value “Akers”. Hence an error is returned.

If we add one row to the lookup table range, the first pass of Vlookup will split the table of 5 rows in half, rounding up to contain the top three rows in the first pass. Now we use the formula


The value in cell B4 is a perfect match, so the value in column C is returned. Bingo! We have a correct result for an unsorted table, without the fourth parameter!

Magic? No, it is pure coincidence. Omitting the last Vlookup parameter on an unordered list MAY return the correct result, but the chances of that are rather slim.

In this case, it is pure luck that the lookup value matches the last entry of the range that Vlookup considers in this pass.

If you use Vlookup without the fourth parameter, you must ensure that the lookup table is sorted by the first column. Otherwise, any result you get back is as reliable as getting the lotto numbers from a toddler.

It may work, but in most cases it won’t.

Share this:

Office for iPad – a first look at Excel

Just installed Excel for iPad. I have an iPad 2 running iOS 7 and installing Excel from the App Store went quickly and smoothly.

Opening Excel on the iPad for the first time, I found the user interface clean and intuitive. After playing with it for a few minutes, here are my first impressions.

  • It’s easy to connect to your OneDrive, but you can also store files locally and use them off-line
  • The insert ribbon contains commands to insert tables, charts, pictures (from the iPad Camera Roll), shapes and text boxes. It even shows a button for Recommended Charts. The Charts button has every familiar Excel chart that you know from the full desktop version.
  • the Formulas ribbon contains buttons for the different formula categories and a little calculator icon on the right to recalculate the sheet.
  • When a table, chart, shape etc. is selected, a context sensitive ribbon tab appears with an impressive array of commands for the selected item, including styles, colors and much more.
  • Although data validation and conditional formatting cannot be defined on the iPad (as far as I can see), they work great if a file already has conditional formats or data validation set up.
  • If named ranges have been defined in a workbook, these work in Excel for iPad and they can be used to create new formulas on the iPad.
  • Excel for iPad supports freezing panes, either first row, first column or both.

Here’s a screenshot of a file I created on my computer and then emailed to myself. I started off with this:

Workbook prepared with Excel 2013 for Windows

Workbook prepared with Excel 2013 for Windows

I added some data, created a table from that data and created a chart from the table. Added a shape, moved the data validation cells to a different position, changed numbers in the conditionally formatted ranges and added a new formula using named ranges and the =Formulatext(C13) to show the formula I added.

Excel on iPad

Excel on iPad

Here is what it looks like with the data validation drop down opened. The screen is overlaid with grey and the data validation box really stands out.

Excel on iPad

Excel on iPad

VBA and external data sources are not supported in this release. When opening a file that contains VBA or links to external sources, a friendly message appears that informs the user, but after that the file will open fine.

Overall, I am very impressed with this Excel experience for iPad. You would not want to create complex models from scratch using just iPad, but it is now possible to take your Excel files with you, store them on your iPad and work on them off-line, doing more than just simple data edits.

Addendum Sunday 30th March 2014: This review is a very quick recap of how Excel for iPad deals with some of the most frequently used Excel functions. Bill Jelen, aka MrExcel has a review inspecting much more comprehensive scenarios here.

Share this:

Slides and files from MVP Com Camp – Power Map

Thanks to all the interested people who turned up at this fantastic event today in Auckland. It was a glorious day and I would not have blamed you if you had chosen the beach over a conference room.

I’ve posted my slide deck and all the files I demoed or used in videos in my OneDrive here: http://bit.ly/PZOrZm

Darrell took a few photos. I’ve pinched these:




Share this:

SharePoint: Redirect if user clicked a button previously

I just came across this question in sharepoint.stackexchange.com. When a user visits a SharePoint site, they are presented with a splash screen and need to accept the policy before they can proceed. Upon subsequent visits, the splash screen does not show, because SharePoint will remember the  user.

PirateEric outlined a possible solution: Use a SharePoint list to save the user name when the button is clicked. When the page loads, look up the user in the list. If they already exist, redirect the page, if not, show the splash page with the button to accept the policy. If the policy changes and users need to be made aware of that, simply remove all items in the list that tracks the users. All this can be done with jQuery and web services.

That intrigued me and I had a go at actually building this, using Marc Anderson’s SPServices.

How to set it up

Create a SharePoint custom list with two fields, Title and UserName. The former is the out of the box field, the latter is a simple text field.

Create two pages, the Splash page with the button and the page that is the desired destination page for all visitors. In my sample these are called Splash.aspx and MainContent.aspx

On the Splash page the code that you can see below will be loaded before any other web part. If you use a Content Editor Web Part to load the code with a content link, make sure that it’s the first web part on the page.

In many cases, JavaScript and jQuery will be placed in the last web part of the page and run after the DOM has loaded. But in this case this would mean that the Splash page appears briefly, even if it is followed by a redirect to a different page.

The Splash page provides the policy (or terms and conditions) that the user must accept, and a link or a button that the user can click to accept. This link or button must then trigger a JavaScript function. That is very easy to do. I used a button and put the html straight into a CEWP like this:

<button onclick="PolicyButtonClick()" type="submit">
   I accept the policy

So the user clicks the button and the JavaScript function PolicyButtonClick() will run. This function can be found in the code below.

First, the jQuery library and the SPServices library are loaded. Then the user name of the current user is retrieved with the SPServices call using SPGetCurrentUser. It returns a text string in the format DOMAIN\Account.  Next, the SPServices call uses the GetListItem. In the call, a CAML query is constructed that will return only list items where the column UserName equals the current user.  The items returned by that query are then counted. Since the user account is a unique value, we can safely assume that the query will either return one result or no result at all.

If the query returned an item, this means that the user has previously accepted the policy and the script will redirect to the MainContent.aspx page.  If the query did not return anything, the current page will continue to be displayed.

When the user clicks the button to accept the policy,  the user name is written into a variable. Then the SPServices operation to UpdateListItem is called and will create a new item in the list “PolicyAccepted”, storing the previously established account name in the column UserName. Then the MainContent.aspx page is loaded.

The next time the user opens the Splash page, their account name will be found in the PolicyAccepted list and they will not see the Splash page again, unless the entry in the list is deleted.

Here is the complete script:

<script language="javascript" type="text/javascript" src="/path/jquery-1.10.2.min.js"></script>
<script language="javascript" type="text/javascript" src="/path/jquery.SPServices-2013.02a.min.js"></script>
<script type="text/javascript">
// start the code even before the DOM is loaded, so not waiting for document ready
//$(document).ready( function() {
// get the user name
 var userName= getUserName();
// find the user name in the list
 var userAccepted = matchUserName(userName);
 if (userAccepted == 1 )
 // redirecting page

function getUserName() {
 var thisUserAccount= $().SPServices.SPGetCurrentUser({
 fieldName: "Name",
 debug: false

function createNewItem(theTitle, theUser) {
 operation: "UpdateListItems",
 async: false,
 batchCmd: "New",
 listName: "PolicyAccepted",
 valuepairs: [["Title", theTitle], ["UserName", theUser]],
 completefunc: function(xData, Status) {

function matchUserName(userName) {
 var queryText = "<Query><Where><Eq><FieldRef Name='UserName'/><Value Type='Text'>" + userName + "</Value></Eq></Where></Query>";
 operation: "GetListItems",
 listName: "PolicyAccepted",
 async: false,
 CAMLQuery: queryText,
 completefunc: function (xData, status) {
 itemCount = $(xData.responseXML.xml).find("rs\\:data, data").attr("ItemCount");

function PolicyButtonClick() {
 var userName= getUserName();
 var theTitle= "Accepted";
 createNewItem(theTitle, userName);
 window.location.href = "http://path/Documents/MainContent.aspx";



Share this:

List all files in folder to Excel

I’m currently working on a project where I need to analyse documents, so they can be properly classified and uploaded to a SharePoint based document management system. Looking at the folder structure with Windows Explorer does not really help much. I need a list of files in their respective locations, so I can sort and filter, pivot and drill down.

There are some VBA code snippets out there which create reports of files in folders and sub folders in Excel, but none of them really do what I need, so I listed out my requirements and re-wrote some code originally posted by Leith Ross at Excelforum.com.

My requirements:

  • Specify a starting folder
  • Have all files and all sub folders listed out, or just the starting folder
  • Show starting folder in one column and the sub folders in separate columns each, so I can create pivot tables, sort and filter on the folder location
  • Show file name, file extension, file size, and core dates in separate columns, so the results can be sorted and filtered
  • Highlight large files with conditional formatting in three colours for > 2MB (yellow), >5MB (orange) and >10MB (red)
  • Keep a column with the original sort order (=location of the files)
  • Enable unlimited levels of nesting
  • Create the report in a new file, so I don’t have to clean up the original

I created an Excel table with these columns:

  • Order
  • File name
  • Extension 
  • Created
  • Modified
  • Size
  • Size MB
  • Path
  • Level1
  • Level2
  • … through to Level20

The table has one row of dummy data in which conditional formatting and one formula for the conversion from Byte to MB is defined. (Yes, I know I could do the conversion with VBA, as well).

Another sheet contains the user interface. It has just one big button to start the macro, which then

  • prompts the user for a starting location, which must start with a drive letter in the format “Z:\” and can contain a longer path.
  • checks that the second character of the location is a colon. If not, it cannot be a valid drive location.
  • prompts the user whether or not to include sub folders (just in case you only want to list out one folder)
  • goes off to work and gathers all the information, writes it into the table, splitting out the data into the columns
  • provides a status bar message with the folder that is currently in progress (the macro can run for quite a long time, depending on the number of files returned)
  • copies the report to a new workbook and removes the placeholder row in the table
  • closes the macro enabled file without saving, so nothing needs to be cleaned up.
If a folder has more than 20 nested sub folders, the macro will still work and write the folder names into the next available column, but the header of the table will have to be added manually.

The file can be downloaded here: CreateFileReport.xlsm

If you want to make changes to the original, make sure to save the workbook before you run the macro, or edit the code and comment out the row that closes the original workbook without saving.






Share this:

Control-Shift-Enter — Win your copy

Ctrl+Shift+Enter - Mike Girvin's book about array formulas

Ctrl+Shift+Enter - Mike Girvin's book about array formulas

Mike Girvin’s new book about array formulas called “Ctrl+Shift+Enter” is something you won’t want to miss if you are proficient with Excel formulas and want to get to the next step. Amazon introduces it like this:

Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions.

You can win one of four free e-book copies of the book if you enter the contest at Debra Dalgliesh’s site here.

Hurry. The competition closes on August 7th, 2013, at 12 noon Eastern Daylight Time.

Share this:

Percentage charts with conditional formatting

Last week I saw some amazing Excel spreadsheets while I was attending the MVP Summit in Seattle and I thought I’d share some ideas. So here are step by step instructions on how to create a percentage chart with just the Excel grid and a sprinkle of conditional formatting. The goal we’re after looks like this:

We’re looking at a grid of ten by ten cells that form a square (roughly). Each cell in this grid represents 1% of the whole. The number below the grid is represented visually by an according number of filled squares (or cells).

Here is how to do it:

Build the grid

First we need the numbers from 0.01 to 1.00 in steps of 0.01 in a grid of 10 by ten cells. If you want, you can type these 100 numbers in manually, but there’s a faster way if you use the fill handle (the little dark square at the lower right corner of the active cell). We only need to type in four numbers. Let’s start in cell C4 and enter 0.91, then manually enter 0.92 in D4. Cells C5 and D5 will get the numbers 0.81 and 0.82 respectively.

Select all four cells, then grab the lower right corner of cell D5 and drag it all the way across to cell L5.

Release the mouse button and you will see the numbers filled in and incremented accordingly:

Leave the cells selected, grab the fill handle in L5 and drag it down to row 13:

Release the mouse button and your 10 x 10 grid is filled with the correct numbers.

That was a bit faster than typing them all in, right?

Prepare the Total cell

Leave one blank row below the grid, then select cells C15 to L16 and use the command Merge cells from the Alignment group of the Home ribbon.

After merging the cells, let’s enter a number, say 38% and do some number formatting. I’d like the cell to show the text “Total:” as well as the percentage number. Click the lower right corner of the Number group on the Home ribbon to open the Format Cell dialog. In the left hand pane select Custom at the bottom of the list. In the Type field enter the text
Total\: 0%

Note: The “\” in front of the “:” sign is needed make sure it appears correctly. If we did not add this, Excel would get confused, since the “:” has a special use in time formats

Hit OK and see how the cell shows text and number, even though the formula bar clearly shows that the cell only contains a number. Crank up the font size a bit. In this example I have used 28 pt Century Gothic.

Format the grid

Now we need to apply some TLC to the grid. Let’s hide the spreadsheet gridlines. On the View ribbon, untick the Gridlines box and the spreadsheet appears in uniform white.

The 10 x 10 grid should have light grey borders, so select the 100 cells …

… and open the Format Cells dialog on the Border tab. Select a light grey colour from the colour drop-down and after that click the Inside and Outline boxes.

The cells now feature a faint grey grid, but the numbers are still showing. Here’s how to hide them for good: Keep the 100 cells selected, open the Format Cells dialog again and activate the Number tab. Choose the Custom option from the number categories and in the Type field enter three semicolons


A number format in Excel consists of four parts to define positive, negative, zero and text values. The different parts are separated by semicolons. If the format definitions between the semicolons are missing, then the number will not be shown at all.

Now the grid is empty and we’ll set row height and column width to make it as close to square as possible:

Select columns C to L and use the Format drop-down on the Home ribbon, where you will find the Width command. Then select rows 4 to 13 and use the same dropdown to select the Height.

Apply Conditional Formatting

The last step is the conditional formatting of the 100 cells. Again, this can be done in one go. Select the grid, then click the Conditional Formatting drop-down on the Home ribbon and select New Rule.

In the next dialog click Use a formula to determine which cells to format. In the formula box enter this formula:


Note the position of the $ signs. Only the $C$15 should have them. Don’t put them anywhere near C4.

Click the Format button and select a darker grey as a fill colour.

Hit OK on all open dialogs.

That’s it. You’re done.

Change the number in the merged cell below the table and see the grid update with more or less grey blocks.

Of course, you can use other colours. Look at my Fruit Machine below. Click the blue cell and hit the Delete key to make the numbers change. Can you get all of them to display the same number?

Depending on your browser and/or screen resolution, you may see some of the grid lines wider than others. To download the workbook just click on the Excel icon in the dark bar below the spreadsheet.


cheers, teylyn

Share this:

Where did the MSDN SharePoint 2010 files go? All replaced with SharePoint 2013?

A few months ago I clicked a bookmarked link to an MSDN article about setting up the development environment for SharePoint 2010. Here is the link:


To my amazement, I found that the article now contains content about setting up the development environment for SharePoint 2013. I tried to navigate to the same tree node in the SharePoint 2010 section, but it does not exist. That change must have happened not too long before, though, because I still found the 2010 article in the Google cache and quickly saved it to OneNote. Phew.

But of course, that cannot work forever. And other saved links, and lots and lots of links on web sites that refer to SharePoint 2010 MSDN pages now show 2013 version content. And no way of selecting a drop-down to show the content for another version.

When I looked through some of the Excel help, I noticed that every 2013 help page I visited had a drop-down to select the version, for example http://msdn.microsoft.com/en-us/library/ff194068.aspx . When the drop-down is selected, the URL changes, but not by much. The string “(v=office.14)” is added before the .aspx to produce http://msdn.microsoft.com/en-us/library/ff194068(v=office.14).aspx , which will show the 2010 version of the page.

The drop-down is not there on the SharePoint MSDN pages, but the URL change works all the same. So, if your saved MSDN bookmarks or links to MSDN about SharePoint 2010 now show SharePoint 2013 content, just edit the URL and insert


before the .aspx

For the article about setting up the development environment, I changed the URL from


to read


and hey, presto, there it was in all its glory and original content.

Now I can delete my OneNote page with the copy. (Or maybe I’ll keep it. Just in case…)

Cheers, teylyn

Share this:

Combine a stock chart with a line chart

Stock charts can take some time to get used to and are among the more unwieldy charts in the Excel chart palette. They get even more challenging if you want to extend the out of the box chart. Recently, a user asked how to combine a stock chart with a line or XY scatter chart. Trying this out I created a stock chart, then copied the data for an additional series and pasted it into the chart with Paste Special > New Series. I then tried to change the chart type of the selected series to an XY chart, but found that a stock chart cannot be combined with another chart. Excel presents an error information message, saying that “Some chart types cannot be combined with other chart types. Select a different chart type”.

Playing around a bit more, though I found that it is actually possible to just select the newly created data series and format it.

After adding the new “data” series to the chart, select the chart. Then click the “Layout” tab in the Chart Tools ribbon.

On the left hand side of that ribbon, select the newly inserted series in the drop-down of chart elements:

Then click “Format selection” just below the drop-down:

In the format dialog, the series can be given a line color and markers, just like an XY or line chart would take, so there’s really no reason to assign a different chart type to the series. The result looks like this:

Here’s the workbook. Tried and tested in Excel versions 2007, 2010 and 2013. You can change the values in the cells below and see the chart update right here and now. Depending on the speed of your interweb connection, you many need to take a breath or two, before the chart updates. :-) . You can download a copy of the workbook by clicking the green Excel icon in the black bar below the workbook.

cheers, teylyn

Share this:

Showcase high and low values in Excel charts – Part 4


In the last part of this mini-series I’m showing how to draw attention to high and low values in Excel charts by using background fill. The first part of this mini-series showed how to present minimum and maximum values of a chart by simply sorting the data values in ascending or descending order. Part 2 looked at conditional formatting the columns themselves if they were the highest or lowest value in the chart. In Part 3, an image was used as a background fill on a stacked series.

Background fill

So let’s look at how to use a background fill for the highest and lowest data points. This can be achieved by plotting a column chart on the secondary axis. First, we need to prepare the data.

The minimum values are identified with


The formula for the maximum values is


The prepared data table looks like this:

Plot all three data columns as a clustered column chart. Then select the value series and send it to the secondary axis. Format the “min” and “max” series to have 100% overlap and a very small gap, like 10%.

Format the primary Y axis to a maximum of 1.

The tricky thing with this variant is to have the secondary Y axis appear on the left. To do this, follow these steps:

  • On the Layout tab, select the Axes drop-down and show the secondary X axis. Now all the columns will be hanging upside down.
  • Format the secondary X axis and let the vertical axis cross at category number 1
  • Select the primary Y axis and format it to have no tick marks and no labels
  • Format the secondary Y axis (which is now positioned at the left of the chart), and format it to let the Horizontal axis cross at axis value 0 (zero), which will put the columns on the base line again.
  • Delete the primary X axis and fine tune the chart formatting as desired.

The result will look something like this:

Take your pick

I hope you have found this mini-series useful and will be able to apply the techniques to your charts.

The attached Excel workbook illustrates all charts above on separate sheets.


Conditional Chart Highlights Part 4

Share this: