[aclug-L] Clinton is a Klingon and Micosoft has proof Fwd: OfMM #10.06 - Match and Index lookup in Excel

[David Carmichael <dec2955@xxxxxxxxx>]

---------- Forwarded message ----------
From: Office for Mere Mortals <wow.robot@xxxxxxxxxxxxxxx>
Date: Wed, Mar 25, 2009 at 2:09 PM
Subject: OfMM #10.06 - Match and Index lookup in Excel
To: David Carmichael <dec2955@xxxxxxxxx>

   Office for Mere Mortals

The beginners Microsoft Office newsletter, since 2000.

Vol. 10 No. 6 - 25 March 2009
  *Office Watch*
- Home <http://news.office-watch.com/>
- Beginners / Mere Mortals <http://news.office-watch.com/zd.aspx?8>
- Buying Office <http://news.office-watch.com/zd.aspx?13> - New!
- RSS <http://news.office-watch.com/rss.aspx?z=8> |
Mobile<http://m.office-watch.com/>|
Twitter <http://news.office-watch.com/?772>
- Ebook Shop <http://shop.office-watch.com/>
- MS Office bookshop <http://news.office-watch.com/amzstore.aspx>

Ads ? the people and products that keep Office Watch as a free service for
all comers




------------------------------



*Creative CERTIFICATES with Word
*Make certificates for work, club or school that don't look like they were
made in Word. Step-by-step, tips plus an exclusive gallery of examples ready
for you to edit and use.  Discount for all Office Watch readers.
http://shop.office-watch.com/ccw* *



*OUTLOOK on steroids*
In-depth help and tips on three important parts of Outlook:
Clever Outlook CONTACTS <http://shop.office-watch.com/coc>
Organizing Outlook EMAIL <http://shop.office-watch.com/ooe>
Effective Outlook CALENDARS <http://shop.office-watch.com/eoc>
get them today, only $4.95 each for Office Watch readers.







*Subscriber savings*

All *Office for Mere Mortals* subscribers get a discount on our popular
ebooks - it's our way of thanking our readers for their support over the
last decade. Click here for your
savings.<http://shop.office-watch.com/eb/DiscountVerify.asp?dec2955@xxxxxxxxx>

*In this issue:*

   - Match and Index lookup in Excel
   - Klingon = Clinton
   - Even cheaper ?Military? Office
   - A free way to give Office 2007 the classic look
   - Print a booklet from a Word document ? free!
   - Cross Referencing in Word
   - Fitting a Word or Excel doc to your printer
   - Office 14 news round-up
   - Calculating with Vlookup
   - More MRU Magic
   - Paper sizes on another computer
   - Fake HR emails with virus
   - Home Use Program myths debunked
   - New spam filter for Outlook 2007
   - How much does your printer really cost?
   - Using Office on netbook computers, part 3
   - Understanding Sections in Word
   - Office 2007 Resource Kit ? free download
   - Monitors, efficiency and Microsoft Office
   - Get Office 2007 from your boss for $30

Keep Office Watch Alive and
Free<https://mail.google.com/mail/?ui=2&view=js&name=js&ver=fi-CboYHL3c.en.O&am=X_k6pcbzcaGYBf3jwfQyA82GVySV#1203f0e0ee463258__Alive>|
Administrivia<https://mail.google.com/mail/?ui=2&view=js&name=js&ver=fi-CboYHL3c.en.O&am=X_k6pcbzcaGYBf3jwfQyA82GVySV#1203f0e0ee463258__Administrivia>|
Editorial<https://mail.google.com/mail/?ui=2&view=js&name=js&ver=fi-CboYHL3c.en.O&am=X_k6pcbzcaGYBf3jwfQyA82GVySV#1203f0e0ee463258__Editorial>|
Advertising<https://mail.google.com/mail/?ui=2&view=js&name=js&ver=fi-CboYHL3c.en.O&am=X_k6pcbzcaGYBf3jwfQyA82GVySV#1203f0e0ee463258__Advertising>
Match and Index lookup in Excel

by David Goodmanson

On their own, the INDEX() and MATCH() functions are fairly straightforward
but when they are combined they create a very powerful Lookup tool. Both
functions have 3 parts to their arguments.

INDEX(range, row_number, column_number)

and

MATCH(lookup_value, range, match_type)

Let?s use each function individually in the table of distances shown below.
[image: Table_1.jpg]<http://news.office-watch.com/articlefiles/805-Table_1.jpg>

Figure 1 - table of distances from http://www.hm-usa.com/distance/ca.html

For example,

=MATCH(A7, A2:A12, 0) returns 6, meaning that in A2 to A12 in column A the
cell A7 (Needles) is the 6th row in the range. The zero in the formula
denotes an *exact match* is to be found.

=INDEX(A1:L12,4,10) returns 290. How does that work? The first argument in
INDEX() is the range or data array that we want to lookup. The 2nd and
3rdnumbers are the row and column offsets. Easy right? In this article
we will
only use MATCH() and INDEX() in the way just specified (more to come in the
future).

OK, so far so good. Now the one thing we don?t want to do is have to
meticulously put in the actual cell references to find out what the distance
is between 2 California cities, we want Excel to do the work. So, let?s use
MATCH() and INDEX() together to make an Excel application.
Pick from a list

Before we go any further let?s make it easier to choose the cities. You
could type in the two city names but just one letter wrong will make the
whole thing fall apart. Here?s how to make a pull-down list based on the
list of cities in the Excel worksheet.

We are going to use Data Validation to put in a combo box on the worksheet
to give us a list to select a City From, and a list to select a Destination
City. So if you have copied the data from the hyperlink shown at figure 1
into a worksheet, that will be the first task.

Next, select a cell near the data table where we will put the list of Cities
From. Then, in your menu, go to Data | Validation and you?ll get an input
box like this:
[image: 
Data_Validation.jpg]<http://news.office-watch.com/articlefiles/805-Data_Validation.jpg>

On my worksheet I have the list of ?From? cities in cells C7:C17. In the
Input Message tab of the box put the word *From* in the input message
section. This will display the message From when the mouse hovers over it.
Click OK, and select the cell underneath. In the same way enter the ?To?
cities range. On my application it is D6:N6.
Back to the lookup ...

Finally, skip one cell down from the ?To? range to the next cell and we will
enter the magic lookup formula which will drive the application.

=INDEX(D7:N17,MATCH(C2,C7:C17,0),MATCH(C3,D6:N6,0))

OK, so how does this work? Well remember, INDEX() uses three arguments in
its syntax. 1) Array or range of data, which in this case is D7:N17; this is
the data table which returns the answers to ?How far is it from Barstow to
LA? 2) is the row reference which MATCH() returns and 3) is the column
reference which MATCH() returns also.

So the 2nd part of INDEX() is MATCH(C2, C7:C17,0). C2 is the list box we
created using Data Validation. If we select Barstow then MATCH() will look
in cells C7:C17 in our table to find an *exact *match to Barstow. It finds
Barstow in row 1 of our nominated range. So 1 becomes the 2nd argument of
INDEX(). Similarly MATCH() returns Los Angeles in the 3rd part of INDEX().

You can adapt this model to any grid or table of data and use INDEX() and
MATCH() interactively to build a lookup model superior to Vlookup.
[image: 
Table_App.jpg]<http://news.office-watch.com/articlefiles/805-Table_App.jpg>

See a version of this article on the Office-Watch.com web site click here
for more <http://news.office-watch.com/t/?a=805&z=12> or mobile
edition<http://news.office-watch.com/?M805>
Klingon = Clinton

Last year we had the politically charged question of Obama and
Osama<http://news.office-watch.com/?z=12&a=653>then later the omission
of Michael Palin (or was it Sarah
Palin <http://news.office-watch.com/?z=12&a=691>?) from the Word dictionary.

We?ve noticed another one that will offend Democrats and Star Trek fans,
perhaps in equal measure.

Type Klingon into Word 2007 or Word 2003 (US English) ? you?ll get a red
squiggly line. That?s bad enough ? you?d think there were more
Trekkies/Trekkers at Microsoft to have noticed that dictionary omission <g>.

Right-click on Klingon and see the first suggested alternative:

*Clinton*
[image: Word 2007 - Klingon equals
Clinton.jpg]<http://news.office-watch.com/articlefiles/804-Word%202007%20-%20Klingon%20equals%20Clinton.jpg>

There you have it ? proof at last of a link between the Bill Clinton, the
current US Secretary of State and the most warlike species in Federation
Territory. And it?s confirmed by Microsoft itself ? so it must be true <g>.

Obviously we?re kidding ? no complaints please.

Do you have a favorite MS Office spell check or thesaurus combination?  Let
us know <http://news.office-watch.com/feedback.aspx> and we'll share them
with Office Watch readers around the globe.

See a version of this article on the Office-Watch.com web site click here
for more <http://news.office-watch.com/t/?a=804&z=12> or mobile
edition<http://news.office-watch.com/?M804>
ELSEWHERE @ OFFICE WATCH

The Office Watch <http://news.office-watch.com/> web site is regularly
updated with MS Office news and tips. We have a new section 'Buying
Office<http://news.office-watch.com/zd.aspx?13>'
with tips and information on how to save when buying Microsoft Office.
Here?s some of the recent articles with links to the regular browser and
mobile/PDA versions:
Even cheaper ?Military? Office

As if the Military Appreciation Edition wasn?t cheap enough, Microsoft has
cut the price yet again. click here for
more<http://news.office-watch.com/t/?a=802&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=802&z=12>
A free way to give Office 2007 the classic look

There?s a free add-on that returns classic Office 2003 menus and toolbars to
Office 2007. click here for more<http://news.office-watch.com/t/?a=799&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=799&z=12>
Print a booklet from a Word document ? free!

A clever option to print a foldable booklet that won?t cost you
anything.click here for
more<http://news.office-watch.com/t/?a=803&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=803&z=12>
Cross Referencing in Word

Cross-referencing is an imposing name for something we're all familiar with
in books, especially reference books. Microsoft Word makes it much easier to
do. click here for more <http://news.office-watch.com/t/?a=797&z=12> or mobile
edition <http://news.office-watch.com/t/pda.aspx?a=797&z=12>
Fitting a Word or Excel doc to your printer

How to print a document when the paper sizes are different. click here for
more <http://news.office-watch.com/t/?a=796&z=12> or mobile
edition<http://news.office-watch.com/t/pda.aspx?a=796&z=12>
Office 14 news round-up

Pricing hint, versions of Windows, Office on the web and other Office 14
news. click here for more <http://news.office-watch.com/t/?a=795&z=12>
or mobile
edition <http://news.office-watch.com/t/pda.aspx?a=795&z=12>
Calculating with Vlookup

Using Vlookup in Excel to work out tax, fees or commission from a table by
David Goodmanson click here for
more<http://news.office-watch.com/t/?a=792&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=792&z=12>
More MRU magic

How to remove single entries from the most recently used list. click here
for more <http://news.office-watch.com/t/?a=785&z=12> or mobile
edition<http://news.office-watch.com/t/pda.aspx?a=785&z=12>
Paper sizes on another computer

How to format a Word or Excel document to print on someone else?s
computer.click here for
more<http://news.office-watch.com/t/?a=790&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=790&z=12>
Fake HR emails with virus

A new wave of fake job applications and responses isn?t caught by
Outlook.click here for
more<http://news.office-watch.com/t/?a=786&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=786&z=12>
Home Use Program myths debunked

We clear up some of the misunderstandings about the MS Office Home Use
Program, one of the cheapest ways to buy Microsoft Office. click here for
more <http://news.office-watch.com/t/?a=782&z=12> or mobile
edition<http://news.office-watch.com/t/pda.aspx?a=782&z=12>
New spam filter for Outlook 2007

There is a ?new? junk email filter update for Outlook 2007 ? but questions
remain about these updates. click here for
more<http://news.office-watch.com/t/?a=780&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=780&z=12>
How much does your printer really cost?

A new online calculator shows how much your inkjet printer really
costs you.click here for
more<http://news.office-watch.com/t/?a=781&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=781&z=12>
Using Office on netbook computers, part 3

Getting the most from the small screen. We look at options for making Office
2003 or Office 2007 work better in the smaller netbook screens. click here
for more <http://news.office-watch.com/t/?a=773&z=12> or mobile
edition<http://news.office-watch.com/t/pda.aspx?a=773&z=12>
Understanding Sections in Word

Sections are in every Word document - here's how to use them to make Word do
what you need. click here for more<http://news.office-watch.com/t/?a=769&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=769&z=12>
Office 2007 Resource Kit ? free download

Microsoft has released the Office 2007 Resource Kit as a free
download.click here for
more<http://news.office-watch.com/t/?a=765&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=765&z=12>
Monitors, efficiency and Microsoft Office

Are you a more efficient Office user with larger or more screens? click here
for more <http://news.office-watch.com/t/?a=758&z=12> or mobile
edition<http://news.office-watch.com/t/pda.aspx?a=758&z=12>
Get Office 2007 from your boss for $30

If you work for a company or organization you might be able to get Office
2007 Professional for use at home for less than $30. click here for
more<http://news.office-watch.com/t/?a=738&z=12>
or mobile edition <http://news.office-watch.com/t/pda.aspx?a=738&z=12>

Back to 
Top<https://mail.google.com/mail/?ui=2&view=js&name=js&ver=fi-CboYHL3c.en.O&am=X_k6pcbzcaGYBf3jwfQyA82GVySV#1203f0e0ee463258_top>
------------------------------
Keep Office Watch Alive and Free

Since 1996 we?ve given Microsoft Office users the straight scoop, whether
Microsoft likes it or not.  Our email newsletters are absolutely free to all
comers and we never misuse your email address.  Our uncompromising stance
costs us a lot of money ? Microsoft is a big advertiser, but not with us.
Other companies won?t advertise with us for fear of annoying Microsoft.

You can support us the way thousands of other people do, support our
advertisers by clicking on their ads and considering their products.   That
includes buying some of our OFFICE WATCH ebooks.

   - Office BACKUP Handbook <http://shop.office-watch.com/obh/>
   - Desktop SEARCH Handbook <http://shop.office-watch.com/dsh/>

Word

   - Eye-Catching Signs with Word <http://shop.office-watch.com/esw/>
   - Creative Certificates with Word <http://shop.office-watch.com/ccw/>
   - Formatting Magic with Word <http://shop.office-watch.com/fmw/>

Outlook

   - Effective Outlook CALENDARS <http://shop.office-watch.com/eoc/>
   - Clever Outlook CONTACTS <http://shop.office-watch.com/coc/>
   - Organizing Outlook EMAIL <http://shop.office-watch.com/ooe/>

Administrivia

*YOUR SUBSCRIPTION
*If you want to stop receiving Office for Mere Mortals, change email
address, feedback, privacy statement etc click
here<http://news.office-watch.com/info.aspx?ofmm=dec2955@xxxxxxxxx>.
This copy of Office Watch was originally sent to dec2955@xxxxxxxxx

Make sure your Office Watch newsletters don't end up in your Junk Email
folder - set us up as a 'Safe Sender' a special
article<http://news.office-watch.com/?514>on our web site explains
what to do plus how to make rules for our different
newsletters.

Office Watch <http://news.office-watch.com/> publishes on the
office-watch.com <http://news.office-watch.com/> web site with a mobile
edition too m.office-watch.com  RSS Feeds come with either links to the main
site <http://news.office-watch.com/rss-explain.aspx> or the mobile
edition<http://news.office-watch.com/rss-explain4PDA.aspx>pages.
*NEW!* Be the first to know about new articles via our
Twitter<http://news.office-watch.com/?772>account.
Editorial

Your comments on Office for Mere Mortals articles are welcome
here<http://news.office-watch.com/feedback.aspx>
.

To submit a story for possible publication please email us
submission08@xxxxxxxxxxxxxxxx

Press releases related to Microsoft Office products (third-party software,
books etc) can be submitted to PressRelease08@xxxxxxxxxxxxxxxx
Advertising

You, too, can reach the largest group of influential Office users on the
planet for a mere pittance... send a message to Victor
ads@office-watch.comand our ad folks will send you details.
 ------------------------------

We happily use Dundee Internet <http:/www.dundee.net/isp/default.asp> for
all web & list hosting.

*OFFICE Watch*  Copyright *© *2009 Office Watch. All rights reserved. ISSN
1328-1674.
Microsoft Office and doubtless many other names are registered trademarks of
Microsoft Corporation.  Office Watch is definitely *not* affiliated with
Microsoft - which is one reason why we are so useful to Microsoft Office
users around the world J

Leaving us?  Forward (don't reply) this message to
leave-31730589-106670540.7adb9efde40edf68ddd3840def4531e4@xxxxxxxxxxxxxxxxxxxxxx
[dec2955@xxxxxxxxx]



-- 
I live in my own little world. But it's OK. They know me here.
Growing older is mandatory...Growing up is optional...
Laughing at yourself is therapeutic!
The geek shall inherit the earth...

-- This is the discussion@xxxxxxxxx list.  To unsubscribe,
visit http://www.complete.org/cgi-bin/listargate-aclug.cgi