Excel VLookup Function

A wonderfully powerful and easy to use way of comparing data across Excel workbooks is the VLookup function. I've been helping a lot of people with this function lately, so I thought I'd get a quick tutorial out there.

Let's start with the syntax of the VLookup function:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Here's an example of how you would use VLookup to find rows on the "OtherSheet" worksheet that match something in the first row of your main worksheet:

=VLOOKUP(A1, OtherSheet!A$2:B$100, 2, FALSE)

Super. So what does this all mean?

lookup_value: The cell you want to match to the other sheet.
table_array: The range where your lookup_value exists in the first column.
col_index_num: The column of table_array that you want to show if you have a match. (First column is 1.)
range_lookup: Optional. TRUE will find matches “similar” to your lookup_value, and FALSE will only find an exact match.

As an example, you have a list of states with their populations on Sheet1. On Sheet2, you have a list of states with the number of lakes they have. Sheet1 lists all states but some are missing from Sheet2.

Option 1: Manually match the states together and copy the values over. 1 by 1.

Option 2: Use a VLookup function. In column C of Sheet1, you would use the vlookup function to see if the value in column A of that row (lookup_value) is found anywhere in column A of Sheet2 (table_array). Also included in table_array is the column that lists the number of lakes. (If there were 10 states listed, the range might be A1:B10.)

Warning!: When typing the range in your VLookup function, remember to put the dollar sign in front of the row numbers. Otherwise, as you copy the function down Sheet 1, the range will move down with you. So, when you get to row 10, rather than having the range of A1:B10, the range will be A10:B19. Use the dollar sign ($) and Excel knows you want the numbers to remain static.

Advanced VLookup Hack:
Whenever your VLookup doesn't find a match, it insists on putting #N/A in the cell. Needless to say, if you're making a report for someone, you don't want all of these ugly values throughout your sheet. You could sort your data and delete all of the #N/A values manually, but you'd also be deleting the VLookup formulas. Therefore, if your data changes you might miss values that you otherwise would have matched.

To get rid of the #N/A values automatically, use this formula:

=IF(ISERROR(VLOOKUP(…)), “Val if not found”, VLOOKUP(…))
For Example:
=IF(ISERROR(VLOOKUP(C13,'State-List'!C$2:E$100, 3, FALSE)),"",VLOOKUP(C13,'State-List'!C$2:E$100, 3, FALSE))

In the above example, we check to see if the VLOOKUP results in an error. (ie, value not found)
So if the “IF” does result in an error, we put the “Val if not found” value in the cell.
(Generally, you’d simply use back-to-back quotes to enter a null value into the cell.)
Otherwise, we run the VLOOKUP as originally intended.
(Replace the dots with the actual VLOOKUP parameters)

You don't even need to understand this formula to use it. Simply type it exactly as shown above, except, insert your own VLookup function in both places where it says VLOOKUP(...) and remember that whatever you type within those quotation marks will show up if there is no match for your VLookup. I generally recommend back to back quotes for a null value, or a zero if you're working with numbers.

Well I hope that helps clarify what Excel's VLookup function is all about. If you found this post helpful, please consider subscribing to my RSS feed. That way you won't have to remember to come back when I post another great Excel tip.

Plantronics TeleWho Contest

A while ago, a blog I frequent, WebWorkerDaily, wrote about a contest that Plantronics is having. Their goal is to come up with a replacement for the word "telecommuter". They are giving away what WWD called the web worker's dream phone, the Plantronics Calisto Pro, to 10 finalists. The grand prize winner gets $1700 in great tech gear. Check out the contest site for details.

So, I decided to enter the contest with the term "NetWorker". Unfortunately, I don't remember exactly what I wrote to enter the contest, but when I was done, I was pretty happy with my submission. I clicked Submit and basically forgot about the contest. Until yesterday...when I got an email from Plantronics telling me that I was selected as one of the top 10 finalists! Out of 500 entries, they selected my term! I don't believe for one second that I was the only person to submit "NetWorker". They must have picked mine either because I submitted it first, or my commentary was so compelling. :-)

I NEVER win contests, so I was absolutely amazed to get that email. By being a finalist, I already won the Calisto Pro. This amazing phone can connect to your land line, cell phone, and even VOIP services. I'm very happy to be a finalist, but I would love to be the grand prize winner. The grand prize consists of an iPod Touch, Altec Lansing home theater system, moondance glow alarm clock, expressionist bass computer speakers, and the ultra-portable "Orbit" speaker. The total value of this package is over $1700. If you have a spare moment between now and November 7th, I'd really appreciate if you could go to the Plantronics contest site and vote for the term "NetWorker". If you don't think it's the best replacement for the word "teleworker" that's fine. I won't be mad if you vote for something else. I'm just hoping to drive a little more real traffic to the site to make it more difficult for anyone to game the system.

And, to help Plantronics fulfill their real reason for this contest, you should check out the Calisto Pro. It really is a fantastic phone.

Foxmarks with Password Sync

I recently upgraded to the new version of Foxmarks with "Password Sync", and it's so great, I find it worthy to tell you about it. Note, this is for the Firefox browser only. With the new Password Sync option in Foxmarks, you can save your passwords to their network and use them on any computer where you've installed Foxmarks.

There are many other password managers out there, but most are simply an encrypted list of your passwords. If you want to log into a site, you have to look up your saved password and enter it manually. Not only does Foxmarks sync your bookmarks across any number of computers, the new functionality of Password Sync automatically inserts your saved passwords when you visit an applicable site.

There's not much else to say, other than...Install Foxmarks with Password Sync Now!

Outlook Attachment Reminder Macro

How many times have you written an email about "the attached file" only to forget to attach the file before hitting send? If you follow the instructions in this post, that'll never happen again!

We're going to add a VBA macro to Microsoft Outlook that searches your email for the text "attach" when you click "send". If it finds that string of text (ie. attached, attachment...) it will check to see if you actually attached a file. If there's no file attached, you'll be prompted with a message box. Then, you can either continue without attaching anything, or you can stop the email from sending so you can include your file. Yes, it's that simple.

*Note: This will not work in Outlook Express as it doesn't support macros.

How to set it up:

1. Open Outlook.
2. Press Alt+F11 : This will open the Visual Basic editor.
3. Expand the project until you find "ThisOutlookSession" and select it.
4. Copy the code below into the Visual Basic code window.
5. Save.

Now to test:
6. Close & Reopen Outlook for good measure.
7. Write an email containing the word attach.
8. Click send. *This is when you should get the pop-up.

*Note: If you have an image in your email signature, that would count as an attachment. Just change "intStandardAttachCount" from 0 to 1.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
' Pops up a reminder if the word "attach" is found but there is no attachment on your email.
Dim m As Variant
Dim strBody As String
Dim intIn As Long
Dim intAttachCount As Integer, intStandardAttachCount As Integer

On Error GoTo handleError

'Edit the following line if you have a signature on your email that includes images or other files. Make intStandardAttachCount equal the number of files in your signature.
intStandardAttachCount = 0

strBody = LCase(Item.Body)

intIn = InStr(1, strBody, "original message")

If intIn = 0 Then intIn = Len(strBody)

intIn = InStr(1, Left(strBody, intIn), "attach")

intAttachCount = Item.Attachments.Count

If intIn > 0 And intAttachCount <= intStandardAttachCount Then

m = MsgBox("It appears that you mean to send an attachment," & vbCrLf & "but there is no attachment to this message." & vbCrLf & vbCrLf & "Do you still want to send?", vbQuestion + vbYesNo + vbMsgBoxSetForeground)

If m = vbNo Then Cancel = True

End If

handleError:

If Err.Number <> 0 Then
MsgBox "Outlook Attachment Reminder Error: " & Err.Description, vbExclamation, "Outlook Attachment Reminder Error"
End If

End Sub

Also, LifeHacker has a script for GreaseMonkey that does the same thing for GMail.


Credit for this Outlook macro goes to Mark Bird. I've seen this macro a few times on the internets, but I believe he's the original creator. You may also find some answers to any questions on his site. Thanks Mark!

There you go! Now anytime you mention an attachment in your email, you'll be sure to have it attached.

Cuil - Another Post About Them Not Measuring Up

Well, it's pretty obvious that they hype over the new search engine, Cuil, was far greater than what the site could actually deliver.

I won't repeat all of the reasons they won't be the next Google killer. If you're reading this, you've surely read about them all several times already. Instead, I did a semi-scientific test of their search results. Since they claim to be the largest search engine in existence, they should have the highest number of results for any given search term. Right???

I chose several words at random as they popped into my head. I searched for these terms on Google.com, Yahoo.com, MSN.com, and Cuil.com and recorded the number of search results. For Cuil, the findings are probably not what they were hoping for. Without any more chit chat, let's get to the charts and graphs!

Search Test Report

From the chart above, you can clearly see that Yahoo! dominates as far as search results go. We're not talking about relevancy, we're only interested in number of search results returned. Google could be considered second, beating MSN and Cuil most of the time. Surprisingly, MSN had the lowest number of total results throughout the entire test. (Even lower than Cuil) Something odd were the search terms where Cuil beat out all other search engines, even Yahoo!.

• "asp.net books" - Cuil: 602,186,415 / Yahoo!: 26,200,000


• "google knol" - Cuil: 38,607,628 / Yahoo!: 15,400,000


• "make liquid soap" - Cuil: 127,604,750 / Yahoo!: 12,500,000

This may not be the most scientific test, or be the definitive demise of Cuil, but if they're the largest search engine in the universe, why don't they give me the most search results??? Please let me know what you think in the comments.

Raw Search Data

	microsoft	cuil	google	yahoo
yahoo	299000000	546032790	2930000000	7020000000
money	638000000	1727246159	1310000000	4620000000
google	189000000	516386388	2750000000	4510000000
web development	532000000	13374732	190000000	4060000000
real estate	291000000	640803910	707000000	3160000000
cars	225000000	814511255	855000000	2540000000
microsoft	237000000	520578288	907000000	2070000000
facebook	20800	15832213	406000000	1590000000
american business	646000000	1917726	98500000	1520000000
myspace	3080000	132450851	861000000	1510000000
internet access	764000000	32763765	176000000	1020000000
batman	25700000	40819411	102000000	469000000
ferrari	68200000	67216311	162000000	335000000
porsche	77000000	75242044	141000000	321000000
linkedin	70300000	5704636	147000000	310000000
britney spears	83000000	136397691	105000000	214000000
foreclosure	16700000	71997961	48100000	198000000
lamborghini	13700000	17650898	62700000	193000000
visual studio	123000000	25181007	43500000	165000000
php books	218000000	81590	23200000	150000000
spiderman	22500000	18529699	50500000	149000000
hacking	142000000	148071687	53000000	144000000
superman	21100000	34224020	61800000	104000000
jason green	19300000	49203	3600000	99000000
riaa	3490000	6881174	8210000	30600000
asp.net books	5080000	602186415	7920000	26200000
led flashlight	4180000	1071520	2510000	25200000
bike commuting	977000	61059	671000	16600000
google knol	6350000	38607628	904000	15400000
excel macro	3200000	88872	649000	13400000
make liquid soap	4620000	127604750	751000	12500000
volcano eruption	2560000	136101	2440000	8390000
johannes brahms	5510000	2119690	4110000	7290000
cuil	71500	124737	1540000	5910000
monty python holy grail	1010000	1898341	412000	4780000
dns cache attacks	2360000	703280	220000	4400000
fryderyk chopin	5200000	1101294	1440000	1270000
grocery shrink ray	164000	434466	59000	777000
Total:	4769373300	6386083562	12225736000	36643717000

Firefox 3...Servers Down

I could tell you to help set the Firefox 3 / Guiness download record, but I think everyone else on earth is blogging about that.

If you haven't downloaded it yet, there's still time to contribute to the record. Just go to SpreadFirefox.com and use the links there. Remember, if you downloaded Firefox 3 from a mirror, or got it before 1:00 PM EST, your download wasn't counted as part of the Guiness record.

Ok, with that out of the way, did any of you try to download the new version of Firefox at EXACTLY 1:00 PM EST (10:00 AM PST)? I tried myself, and also followed the thread on Slashdot to see what everyone else had to say about it.

Apparently, at 12:56 PM (All times are EST from now on.) The Mozilla servers all crashed. Nobody could access SpreadFirefox.com or mozilla.org. We were all being met with server timeouts and http 1.0 errors. If you check out the Slashdot thread, it's actually funny how everyone had to let us know the servers were down.

So the servers remained down until about 3:00 PM when I was finally able to get my hands on a fresh copy of Firefox 3. I didn't really "need" to be the first in line for it, but once I saw the servers were down, I became curious.

I think there were many things Firefox could have done to avoid this.

1. How about, don't schedule an exact time for your release!?!?! EVERY geek following this release just had to be the first one to download the new version. As the clock neared 1:00, the Mozilla servers got pounded because everyone needed to download it at that exact time. As one slashdotter put it, maybe we could also get the Guiness record for the largest Distributed Denial of Service (DDoS) attack performed by real people.

2. That first point was pretty much all I had, but I suppose they could have also planned for such an onslaught of traffic by better distributing the load.

There were also many complaints that "launch time" occurred at inconvenient times of the day around the world. "Launch Day" is June 17th, but it's being advertised as June 18th in Japan. Maybe they could have distributed launch time by making it 1:00 PM of whatever your local time is, like New Year's celebrations?

How do you think Mozilla could have avoided the server downtime?

Is it even a big deal that their servers went down for a couple hours?

Well, it's almost 11:00 PM here now and all of the download sites are working great. If you tried to get Firefox 3 earlier today but were denied, you should be good to go now. I encourage everyone to switch to Firefox. Why it's so much better than Internet Explorer will have to wait until another post.

Google Analytics 101

I just came across a simple yet useful post on the Google AdWords blog about Google Analytics.

If you've installed Google Analytics and you don't know what to do next, their post should help. Basically, just look at the major metrics and watch them over time.

The main thing to keep in mind is that there is no "good bounce rate" or optimal time on site for everyone. Each site, and even each section within a site will have different values. The key is to look at trends over time.