Index...
 

This is by way of a small XL grumble.
Every time I want to use Index or Match, I can never remember which is
which, and always have to look it up.
I thought I may be able to apply logic to help me remember. (So naive)
When I use the index in a book, I look up a word (value), in the index
(table), and get given a page number (reference).
Why the heck does XL do the reverse? What good is a book index where you
look up a page number to see what's on it? Am I missing something? Does
anyone else get these two functions mixed up, or am I pathetically alone?
Regards - Dave.

Index...
 

But as an scientist then I think of a whole bunch of numbers (an array), I
tell my mate to look at the one with index 5,6, Because that is how I think
of index even through I am an author. And that was the way early programmers
used the term - mathematically.
It is all in the eye of the beholder. Microsoft is not being 'evil' just to
get you upset, honest!
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dave" wrote in message
...
This is by way of a small XL grumble.
Every time I want to use Index or Match, I can never remember which is
which, and always have to look it up.
I thought I may be able to apply logic to help me remember. (So naive)
When I use the index in a book, I look up a word (value), in the index
(table), and get given a page number (reference).
Why the heck does XL do the reverse? What good is a book index where you
look up a page number to see what's on it? Am I missing something? Does
anyone else get these two functions mixed up, or am I pathetically alone?
Regards - Dave.

Index...
 

Forget about the book index analogy.

Think of it like this....

INDEX is a storage place for data. It "holds" the data you're interested in
and returns the value you want by telling it where to find that data.

MATCH returns the *relative* position of a value within a 1 dimensional
array.

...........A..........B
1......Tom.......10
2......Sue........88
3......Biff..........0
4......Joe........15
5......Lisa........44


=MATCH("Sue",A1:A5,0) = 2

Sue is in the 2nd position within the array A1:A5

=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)) = Sue

The max value in B1:B5 is in the 2nd position so MATCH returns 2. This 2 is
passed to the INDEX function and tells it we want the value located at
position 2 of the indexed range A1:A5. Sue is located at positon 2.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
This is by way of a small XL grumble.
Every time I want to use Index or Match, I can never remember which is
which, and always have to look it up.
I thought I may be able to apply logic to help me remember. (So naive)
When I use the index in a book, I look up a word (value), in the index
(table), and get given a page number (reference).
Why the heck does XL do the reverse? What good is a book index where you
look up a page number to see what's on it? Am I missing something? Does
anyone else get these two functions mixed up, or am I pathetically alone?
Regards - Dave.

Index...
 

You can use that book analogy, but think of it in the manner that *YOU* are
creating the index.

In a book, the index contains words or subjects (on the left side), and
where they can be found (page numbers on the right side).

In XL, you create the index where the words or subjects pertain to cells
(the range you specify - on the left side), and the Match() function is the
page number (on the right side).

All you have to remember, is that wherever you *start* your range( Row1,
Row100, or Row 1000), that's page *1*.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave" wrote in message
...
This is by way of a small XL grumble.
Every time I want to use Index or Match, I can never remember which is
which, and always have to look it up.
I thought I may be able to apply logic to help me remember. (So naive)
When I use the index in a book, I look up a word (value), in the index
(table), and get given a page number (reference).
Why the heck does XL do the reverse? What good is a book index where you
look up a page number to see what's on it? Am I missing something? Does
anyone else get these two functions mixed up, or am I pathetically alone?
Regards - Dave.

Index...
 

Hi TV and BL,
I'm still mumbling quietly. Can't quite get my head around an index being a
storage place where I have to be the one who knows where each piece of data
is.
Anyhoo chaps, thanks for the replies. I'll get there.
Regards - Dave.

Index...
 

I have to be the one who knows where each piece of data is

That might be where you're getting twisted. You *don't* have to know where
the data is. Just look at my example. If it had involved 1000's of rows of
data I wouldn't know where the max value was so I told Excel to find it for
me using the MATCH function.

Once the "light goes on" you'll realize just how easy it really is.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi TV and BL,
I'm still mumbling quietly. Can't quite get my head around an index being
a
storage place where I have to be the one who knows where each piece of
data
is.
Anyhoo chaps, thanks for the replies. I'll get there.
Regards - Dave.

Index...
 

Hi TV,
Thanks for your persistence in helping me understand this. The light is
coming on - slowly. I'm still getting my head around formulas that have a
number of functions each one passing their answer(s) to other functions which
use them as arguments. I know it's all logical, and I can follow them if I
work through it piece by piece.
Thanks again.
Dave.

Index...
 

I thought of this over night...

You can use the book analogy but forget about using the books index.

Think of it as the book itself *is* the index. A book is a storage place for
data and INDEX is a storage place for data. A book contains pages numbered
sequentially. INDEX contains cells or values that are in a numbered
sequential order.

Think of the pages of the book as the cells in INDEX.

When you want to find something in the book you look for a certain
page/pages. In INDEX you look for a certain cell/cells/value.

In the book you search the pages for the data that you want. In INDEX you
search the cells for the data you want.

How you search is left to your own imagination based on your skill and what
you're actually looking for.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi TV,
Thanks for your persistence in helping me understand this. The light is
coming on - slowly. I'm still getting my head around formulas that have a
number of functions each one passing their answer(s) to other functions
which
use them as arguments. I know it's all logical, and I can follow them if I
work through it piece by piece.
Thanks again.
Dave.

Index...
 

Hey Biff, I thought I was the only one who thought about XL problems in the
shower!
Dave.

Index...
 

It was a dream! Or, maybe a nightmare!

All kidding aside, I've actually solved complicated problems "sleeping on
them".

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hey Biff, I thought I was the only one who thought about XL problems in
the
shower!
Dave.

Index...
 

Dave wrote...
This is by way of a small XL grumble.
Every time I want to use Index or Match, I can never remember which is
which, and always have to look it up.
I thought I may be able to apply logic to help me remember. (So naive)
When I use the index in a book, I look up a word (value), in the index
(table), and get given a page number (reference).
Why the heck does XL do the reverse? What good is a book index where you
look up a page number to see what's on it? Am I missing something? Does
anyone else get these two functions mixed up, or am I pathetically alone?

As with most really basic spreadsheet design decisions, don't blame
Microsoft. They only copied. They didn't invent.

Lotus Symphony 1.0 (not the OpenOffice-based product IBM is currently
foisting on the unwary, but the @#$%&*! package Lotus foisted in the
unwary back in the 1980s) introduced the @INDEX function, which they
added to 1-2-3 in Release 2.0. Microsoft copied the function's name
(sans @) and intent but altered its syntax and semantics.

I don't recall whether Lotus or Microsoft introduced the [@]MATCH
function, but whichever one did, the other copied it.