Excel Tip: Case Sensitive VLOOKUP

When I get stuck on Excel, I usually find myself Googling the forums for an answer, as was the case last week, when I needed a case-sensitive VLOOKUP for a job I was doing for a client.

The Microsoft site suggests a solution that doesn't really work! It only checks that the answer found is the right case. This is no use if your data has the same text in different cases (as my client's did).

However, I did find an ingenious solution, which I thought I would share with you - as well as explain how it works.

I found my answer by DonkeyOte on this Excel Forum discussion.

DonkeyOte's answer was:

"There are a few approaches - assume lookup values in A with values to be returned in B ... criteria is in C1 with result formula in D1:

D1: =INDEX(B1:B100,MATCH(TRUE,INDEX(EXACT(A1:A100,C1),0),0))"

If you are not interested in how it works, you can just use it as is and just replace as follows:

A1:A100 with the column (or row) range you want to lookup in
C1 with the lookup value
B1:B100 with the column (or row) range containing the results

(Use row ranges if you want to replicate an HLOOKUP)

If you want to know how it works, read on...

As you can see, this uses three separate functions - one of them twice.

I have covered using MATCH and INDEX together to create a VLOOKUP before.

Here is my introduction of these two functions from that post:

"INDEX is a function for returning a cell or range from within an array. At its simplest level this is done by referring to the cell by its row and column number (INDEX can do quite a bit more than this and also has another form which allows you to look at multiple ranges, however we only need to use its simple form here - I may do a post on some of its more advanced features at a later date). The simple form of INDEX is as follows:

=INDEX(range,row,column)

Column can be omitted and, if so, it is assumed to be 1 - unless range is just a single column in which case Excel will assume that the omitted argument is the row.

So for example:

=INDEX(A1:D5,2,3) returns the value in C2

MATCH finds the position of a value in a single row or column range. Its syntax is:

=MATCH(lookup value,range,match type)

match type is optional and has the following three possible values:

1 (or omitted)  - finds the position of the largest value that is less than or equal to lookup value and requires the range to be in ascending order (this works the same way as using TRUE for the 4th argument in a VLOOKUP).

-1  - finds the position of the smallest value that is greater than or equal to lookup value and requires the range to be in descending order.

0  - finds the position of the first value that is exactly equal to lookup value (this works the same way as using FALSE for the 4th argument of VLOOKUP). In this case, the range can be in any order."

DonkeyOte's formula uses the principles from my earlier post, but uses the EXACT funtion to deal with the case in a very clever way.

EXACT compares two values and returns TRUE if they are exactly the same (case as well), and FALSE if they are not. Our formula compares a range to our lookup value using EXACT.

EXACT(A1:A100,C1) will return a series of 100 TRUEs and FALSEs, the position of the first TRUE, being the position of the row that agrees to C1, our lookup value.

We can use INDEX to convert that to an array, as a column number of 0 will return the whole column as an array. So,

INDEX(EXACT(A1:A100,C1),0) returns the 100 TRUEs and FALSEs as an array

We can then use MATCH to determine the position of the first TRUE so,

MATCH(TRUE,INDEX(EXACT(A1:A100,C1),0),0) returns the position of the first TRUE

Finally, we use INDEX to return our result, being the contents of the same row in column B,

INDEX(B1:B100,MATCH(TRUE,INDEX(EXACT(A1:A100,C1),0),0))

Very clever, I thought!




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: How Excel handles dates and time

My wife did the Great North Run half marathon on Sunday in 2 hrs 42 minutes and 23 seconds, almost the same time as she took last time she did it when she was 17, 25 years ago. Well done to everyone who did the Great North Run yesterday, I'm definitely doing it next year - once I've lost a few stone!

I mention this partly because I am one proud husband today, partly because she has raised nearly £500 for a cause very close to our hearts, but mostly because this gave me the inspiration for today's post.

I've never really done a post specifically on the interesting way that Excel handles time.

I've touched on it in other posts, but not specifically covered it, so let's do it now.

Let us start with the dawn of time. Now I realise that in parts of the US in particular this can be a contentious subject, but for Excel it is quite simple.

Time begins on 1st January 1900!

Excel cannot handle dates before this, and all subsequent dates are measured from this.

A date in Excel is stored as a number. 1 being the 1st January 1900, 2 being the 2nd January 1900, etc. Today's date in Excel terms (17th September 2013) is 41,534.

You may have noticed that zeros formatted as dates in Excel appear as 00/01/1900, obviously depending on the format chosen (I have used the conventional UK format dd/mm/yyyy). This is the day before the dawn of time, the 0th of January 1900.

Following this convention of 1 representing a day, times are represented as fractions of a day, so 0.5 is 12 noon and 41,534.5 is 12 noon today.

Every time you see a time or date in Excel, it is a serial number on this scale. What you see is determined by the format applied to the cell. If you change the cell format to Number, you can see the serial number behind the date or time.

I won't go into number formats in detail here (I'll cover that in a future post), but 41,534.5 could appear as any of these for example:

17/09/2013 (dd/mm/yyyy)
17 September 2013 (dd/mmmm/yyyy)
17-Sep-2013 (dd-mmm-yyyy)
12:00:00 (hh:mm:ss)
17/06/2013 12:00:00 (dd/mm/yyyy hh:mm:ss)

The benefit of this serial number approach is that you can apply normal formulae to dates and times. For example you can deduct one time or date from another to establish the elapsed time between them.

Hopefully that gives you an idea of what is underlying dates and times in Excel.

I hope you find it useful.



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: Don't do that! How to avoid catastrophes

I received an email from a reader, Nikki, just the other day who has started her own blog.

Nikki blog covers her thoughts on many (non-Excel) subjects but the post she brought to my attention was a cautionary tale about an Excel mishap that recently befell her.

This got me to thinking that it might be useful to look at what things we should avoid in Excel.

I am not talking about good and bad practice here, I am talking about the things that can lead you to irretrievably screw up your spreadsheet! Those that throw all of your formulae out, or better still, cause that wrench in the pit of your stomach, the second you press the Enter key.

I'll start with Nikki's, throw in a few of my own, and then throw it open to the floor in the comments.

Nikki's nightmare was caused by forgetting that she had multiple sheets selected, causing her to overwrite everything she had been working on in the other sheets.

You can read Nikki's full nightmare here, it's not for the faint-hearted!

One of my nightmares that I have written about before is the dangers of cutting and pasting, or dragging and dropping.

For more detail on this one, have a look at my full post on it.

In short, cut and paste, or drag and drop, alter any formulae that refer to the cells being moved. This can mess up your whole spreadsheet, if that's not what you intended. It can be very difficult to pick out what has happened too.

Another one to be wary of is compatibility issues. If you have pivot tables in an Excel 2003 workbook and decide to save this as a .xlsx file in a later version of Excel - don't expect the pivot tables to work if you then try to save it as a .xls file again.

And the last one from me - don't add protection to a workbook or worksheet and apply a password, without making sure you have made a note of it somewhere you will find it next time you need it. It sounds obvious, but we've all done it!

I'm sure you've all got your own, so don't be shy. Please share them in the comments, so that your pain has not been in vain and it can help others to avoid the same fate.



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 Tips - Quickly adding up numbers without formulae

This week, I've got one of those really simple tips, that are just that - if you already know them.

It is these kinds of tips that tend to get the most interesting reactions when someone sees them for the first time, and realise they have been doing something the long way round for so long.

A couple of other examples from the blog were:

A really quick way to copy down a formula

and

A quick way to add dollar signs in a formula

This week's quick tip tells you how to add up any numbers on the screen without having to type in a formula.

There are many times, when looking at numbers on a spreadsheet, that we want to quickly add a few of those numbers, just to check something (we may be trying to spot which numbers make up a difference that we are trying to find, for example). We could, of course, find an empty cell and enter a formula in the cell, using either SUM to add up a range of adjoining cells, or a simple formula in the form =A1+B3+A4+C7.

Excel, does however provide a simple tool for doing this that you might not have noticed (I've certainly worked with many experienced users that hadn't).

If you highlight any cells with numbers in them, Excel presents their sum on the status bar in the bottom right corner below the spreadsheet:

You can highlight them in any of the usual ways:

• by using the mouse to select a range of adjacent cells, which can include multiple columns or rows;
• clicking on the column letters or row numbers to highlight whole columns or rows;
• highlighting multiple cells or ranges, by holding the Ctrl key while you click on individual cells or select ranges as described above.

In Excel 2007 onwards, you also see a count of the cells with values in, and an average. Note that the count also includes cells with text in - not just numbers. The average, however, only averages the cells with numbers in - as you would want.

This sum (and average and count) appears every time you highlight cells (that include numbers) - you don't need to do anything else.

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".