Thanks a lot. I had not received (or at least seen) the reply.
Tony.

"NBVC" wrote:


Tony7659;391425 Wrote:
NBVC,
It works. Just one more thing: would you please explain it to me? I
need to
use this method in a variety of formulas so I would like to understand
what I
am doing. Thank you for your time.
Tony.

"NBVC" wrote:


Try something like:


Code:
--------------------

=INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2' !$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3)
--------------------


where Sheet2!A2:C25 contains your table and A1 on current sheet
contains 2009 and B1 contains 2...


--
NBVC

Where there is a will there are many ways.

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/member.php?userid=74)
View this thread: 'FORMAT - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=109382)



Index() function indexes the range you want to look at
Match() finds the position within a range that matches your lookup
criteria...

so:

=Index(Lookup_Range,Row,Column)

and can be substituted as follows

If the lookupTable is 1-dimensional, you don't need the Column
number... default to 1

=INDEX(Lookup_Table,MATCH(lookup_Value,Lookup_Rang e,0))

Since you have to columns that have to match criteria, we have to be
creative in the 2nd Match() argument above...

We use here, INDEX(('Sheet2'!$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$2 5=B1),0)
to create a lookup Range made up of an array of 1's and 0's, so that we
can Match a 1 against it to come up with a position.

The 2 conditions in the above Index() function return arrays of Trues
and Falses.. which, when multiplied together turn into 1's and 0's based
on multiplying TRUE*TRUE, TRUE*FALSE, FALSE*TRUE and FALSE*FALSE.. the
only one to give 1 is the TRUE*TRUE and that is when you have a match in
both columns (in same row)... The 0 at the end is because you need at
minimum to complete the Row Number condition of the Index() function for
it to work (and you really don't have one, so use 0)... The Match() part
then takes over and looks for a 1 in that array, returns the position of
that 1 and then the first INDEX() takes the corresponding item from the
same position vertically from Column C.

Hope that helps...


--
NBVC

Where there is a will there are many ways.
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109382