Lost of times I work with Excel to make statistics, build nice operational Exchange dashboards, and I make a very intensive usage of LOOKUP and VLOOKUP. Since I recently lost valuable time trying to figure why a LOOKUP would not return me the values I want, or sometimes it did, sometimes it didn’t, I finally found the workaround for this.

This post is mostly for me to remember the trick but it’s good to know for you guys as well if you are like me an Excel-man (ish):

This post is merely a pure copy (commented) from the following article:

http://support.microsoft.com/kb/181212

“LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order. The following information describes different formulas that you can use to return the same information returned by LOOKUP without requiring that the first column of the table be sorted. “

==> because I was not aware of the above until I searched the Internet (using www.bing.com I have to say it), I lost about 2 hours trying to figure what was wrong in my spreadsheet ! I was thinking “Chier, qu’est-ce que c’est encore que ce truc de daube ???” which the Parisian French for “what is wrong with my data ???”

(Private message to my friends Joe T. and Liju V.: we don’t say “sacrebleu” anymore in France since the XVIIth century, you have to learn the expression above instead :-) )

Then I found the above article, which saved my spreadsheet. Pewwww, it’s not a bug, it’s just the way the LOOKUP function works !! I was just ignorant.

I could have used VLOOKUP, but VLOOKUP does a lookup on the first column only. so if you wish to match the data on something in a column of your table that is not in the first column, and you don’t want to rearrange your original table against which you are searching, that’s when you’d use the formula in mentionned this article.

I found the “INDEX+MATCH” combination the most useful and the most practical to use in my case, so I paste this part in this post. For the other possibilities, just open the support article above (181212)

Using INDEX and MATCH

   =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)
				

Where:

   Table_Array    = The entire lookup table.

   Lookup_Value   = The value to be found in the first column of
                    "table_array".

   Lookup_Array   = The range of cells containing possible
                    lookup values.

   Col_Index_Num  = The column number in "table_array" for which
                    the matching value should be returned.
				

Hi all,

Thank you very much for your attendance and your kindness during our Exchange 2007/2010 migration workshop.

As promised, and as it’s easier for me to share these through my blog as you guys already got the address, below is the link to the extra-slides that I showed you during the workshop; these are intended to help understanding Exchange 2013 parts that I found were not obvious, especially because they changes quite significantly since previous Exchange versions, or because they are new…

Hi all,

A quick “tip” that can be useful as IIS logging usually generates GB of files that we don’t necessary monitor, and I thought it was important enough to write a quick post about it, as I got lately several examples of customers for which C:\ free drive space fell below 10%;

You may ask yourself, why this guy is writing such a post on an Exchange Server blog ? What does this has to do with Exchange ?

- reason is Exchange is using the C:\TEMP or C:\Windows\Temp folder for a few things like message content conversion (the HUB role in particular). So if we run out of disk space on C:\, there is a risk that the Transport Service stops; or even worse, the Windows host can stop working because there is no more space to handle temp files for other OS related tasks.

So continue to monitor your disk space on the C:\ drive, and you can use the below command line to check and purge your IIS Logging directory (if you need IIS logging to stay activated) :

get-childitem -Path C:\inetpub\logs\LogFiles -recurse | where {$_.lastwritetime -lt (get-date).addDays(-90)} | Foreach-Object {del $_.FullName}

Also useful if it appears you don’t need IIS logging because there is no troubleshooting need, you can simply deactivate IIS logging:

Enable or Disable Logging (IIS 7)

http://technet.microsoft.com/en-us/library/cc754631(v=WS.10).aspx

Cheers,

Sam.