Greetings from the links in Scottsdale, AZ and Happy almost Valentine’s Day!!

For those of you who are jonesing to play my favorite fortune telling-game of yesteryear (middle school), please download the Excel Rain Man M-A-S-H template on the Tips & Tricks page of www.excelrainman.com.

For more details on how this spreadsheet can help you land Bradley Cooper, push a Prius, and avoid living in a Shack for the rest of your M-A-S-H life, check out Newsletter #27. (Disclosure: It is highly unlikely that you will “land” Bradley Cooper following the use of the Excel Rain Man M-A-S-H template. But… never say never!)

Now, let’s get back to spreadsheet business…
When I meet someone, the first thing I ask, after their name and 40 yard dash time, is whether they know how to do a VLOOKUP! When this is met with a blank and bewildered stare (usual response), I think “ooh goodie, here is a potential client”. Alternatively, when I see the persons eyes light up with excitement (happened once in the last 30 years), I realize that they might be a little crazy… Like me!

No matter what version of Excel you are rockin’, or what computer you are pushin’, the good old VLOOKUP will never go out of style. So if you want to be “in the know” around the water cooler (cuz people are usually chatting up VLOOKUPS as opposed to the most recent episode of Portlandia), see below on how to deal with this posh function.

Of course if you want to have your hand held a little more, contact us to sign up for one of Webinars & Classes or hook yourself up with our super affordable on-demand Video Tutorials.

And if you have no interest in learning how to do a VLOOKUP yourself… you know who to call to get your spreadsheet work covered! Just Submit a Request and we will take it from there.
For all you over achievers… here goes!

VLOOKUP searches a list for a value in the left-most column of a specified array (just a table really) and returns the corresponding value in that same row with a column you specify to the right of the value.

The user can specify whether to search for an exact match or an approximate match.

The format of the VLOOKUP function is:

= VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])
Where:
• Lookup_value (required) is the value searched for in the 1st column of the Table_array.
• Table_array (required) is the reference data set, which must include at least 2 columns. The leftmost column is the column that will be searched for the Lookup_value.
• Col_index_num (required) is the number of the column in the Table_array that contains the value to be returned.
o The 1st column has a Col_index_num = 1
o The 2nd column has a Col_index_num = 2, and so forth
• Range_lookup (optional, defaults to TRUE). Determines whether the formula will search for an exact or approximate match between the Lookup_value and the left-most cells in the Table_array.
o When TRUE or left blank, VLOOKUP will return the closest match to the Lookup_value. The 1st column must be sorted in ascending order to yield the correct result.
o When FALSE, only exact matches will be returned.

Here are a couple of examples. In each case, we start out at the top of the worksheet with our Table_array and then on the same worksheet we have a examples with formulas, results and some commentary.

Example 1: Instruments

Getting to know the basics about using VLOOKUP… For starters, the Table_array is A2:C13.

Example 2: Grades

More examples using a typical grade scale (unless you went to Brown or something). And the beginnings of how to appropriately search for approximate matches. Let’s see what this Range_lookup stuff is about.

 

Example 3: Shopping List

Let’s get fancy and use multiple VLOOLUPS in our formulas!

Example 4: Student GPA

Unique identifiers are the best Lookup_values when using VLOOKUPS!

Key things to know:

When using any function, the purpose is to minimize any manual work that needs to be done. Therefore using more cell references in the Arguments will allow for less work when copying and pasting data.
Absolute references and relative references will enable easy copying and pasting of VLOOKUP formulas. See Newsletter #30 for some absolute and relative reference keyboard shortcuts!

*Details on Named Ranges are coming soon!

Small Biz Spotlight

What is is the only thing worse than forgetting your smart phone? Remembering to bring your favorite device only to realize that your battery is dead!

I take my BlackBerry everywhere… To dinner, to bed, to golf (unless someone is willing to smuggle me in to Deepdale or Merion), heck I would shower with it if I could. That said, when it is sans charge, how am I to tweet my latest keyboard shortcut or help someone through their pressing spreadsheet drama? So frustrating!
Well, thankfully the latest game changer in my life is the Powerstick, a cutting edge and truely portable charger for all of my mobile devices! Here is how it works:

1. Charge the Powerstick from any USB port including computers and laptops
2. Once it is finished charging, you carry it with you everywhere you go. It is about the size of a stick of gum, so it fits easily in your pocket or handbag.

3. Then when your BlackBerry, iPod, Siri-alicious iPhone (if you are a lucky one), MiFi card (they run out of battery so fast), or digital camera needs a quick charge, just attach one of the 6 provied connectors, and charge away!

In addition to replacing all your chargers with one convenient power source, the Powerstick has a micro-controlled processor that provides power only when needed (so it will stop charging the device once it is full). AND as if this isn’t enough, the Powerstick is available in a USB flash drive version as well.

That’s right, the Powerstick is available with 8GB of memory that I can quickly use to store all of my sexy speadsheets on the go!
Just to provide a little street cred, the Powerstick was awarded the 2011 CES Award for Best of Innovation! Also check out the slightly larger PowerTrip that is designed to charge more powerful devices like an iPad, e-reader and GPS units. The PowerTrip can also power up using the built in solar panel!

So stop being left out in the techy cold… chargeless and without your necessary files! Get your Powerstick on and you will be forever prepared, cool, and amply charged wherever you go!

Do your spreadsheets need a charge? Just Submit a Request and let the Excel Rain Crew take it from there!