Just a thought:

These types of formulas are very powerful and useful. If you can learn to
master them you'll be way ahead of your peers.

Biff

"Biff" wrote in message
...
In addition to Bob's explanation......

When we use the Index function the values that are indexed are stored in a
specific order or position. Consider this example that's in the range of
A10:A14:

A10 = 1/1/1997
A11 = 1/1/1997
A12 = 1/5/1997
A13 = 1/7/1997
A14 = 1/8/1997

When we index this range:

=INDEX(A10:A14,..............)

The value of A10 is in the 1st position
The value of A11 is in the 2nd position
The value of A12 is in the 3rd position
The value of A13 is in the 4th position
The value of A14 is in the 5th position

We use the ROW(......) function (in the type of formula that you're using)
as a means of generating an array of numbers that correspond to the
Indexed positions. So, using:

=INDEX(A10:A14,..............)

Which holds 5 values, we need to generate an array of numbers from 1 to 5.
So, we could use ROW(1:5). But using ROW(1:5) means that you would have to
know the exact number of values indexed. You could manually count them but
that's not very "sophisticated".

So, as Bob explained, we can use the actual range reference, A10:A14, and
subtract the offset.. If we used ROW(A10:A14) that would generate an array
of numbers from 10 to 14 and since we don't have positions 10 to 14 in the
Index function that would return a formula error. So, we use
ROW(A10:A14)-ROW(A10)+1, which evaluates to this:

10-10+1 = 1
11-10+1 = 2
12-10+1 = 3
13-10+1 = 4
14-10+1 = 5

Now we have our array of numbers from 1 to 5 that correspond to the 5
positions of the indexed values.

Now, let's combine that with a conditional test:

Assume A1 = 1/2/1997

Our conditional test could be something like this:

IF(A10:A14<A1,ROW(A10:A14)-ROW(A10)+1)

The conditional test: A10:A14<A1, will return an array of TRUE or FALSE:

TRUE
TRUE
FALSE
FALSE
FALSE

Now, the IF function will return the value_if_TRUE as the calculated ROW
number and the value_if_FALSE as FALSE. It will look like this:

1
2
FALSE
FALSE
FALSE

Then, as Bob explained, that array will be passed to the SMALL function:

SMALL({1;2;FALSE;FALSE;FALSE},COLUMNS($A:A))

And in turn, that value will be passed to the INDEX function.

Biff

"Bob Phillips" wrote in message
...
What this does is create an array of row offsets for all rows that meet
the
two conditions

Dates=$B13
and
nomi=$B$6

If you select this part

ROW(Magazine)-ROW(C$4)+1

in the formula bar, and press F9, you will see the row number offsets
evaluated (1;2;3;...). If you then select

IF((Dates=$B10)*(nomi=$B$6),ROW(Magazine)-ROW(C$4)+1)

and F9, you will see only row number offsets of matching items
(1;2;FALSE;FALSE;...)

This array is then used by the SMALL function using the column number as
the
position to get a valid row offset, which is in turn passed to the INDEX
function to get an actual value.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"skarbanan"
wrote
in message ...

Thanks Biff. I read the information about "--" signs and i understand it
know.

I understood your code only not this part :
IF((Dates=$B13)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);2));"")

Well the ROW(Magazine)-ROW(C$4)+1);2) are giving this result 1
Row(Magazine)=4 -ROW(C$4)+1)= c4=4 + 1 = 4

so ROW(Magazine)-ROW(C$4)+1);2)) equals 1, but when i put the number 1
instead of the formula it gives another value...Now i'm asking:is it
because it's an array formula and the searching of the data stops at
every row to continue from the last row or it's somethin else?
Just for curiosty and to understan the formula i triey to put ROW(B2)+1
(which is the same as the next one) instead of ROW(C$4)+1 but it gives
me a wrong result...
could you explain please? I't that i'm really into it and i would like
to learn...

p.s sorry for being boring


--
skarbanan
------------------------------------------------------------------------
skarbanan's Profile:
http://www.excelforum.com/member.php...o&userid=29901
View this thread:
http://www.excelforum.com/showthread...hreadid=496084