Try something like this:

With
Your sample table on Sheet1, in Cells A1:J7

Then
On Sheet2
G3: (a country name)
H2: (a year, e.g. 2004)
J2: (a year, e.g. 2006)

This formula finds each Country referenced in cell G3 and returns the
corresponding values from the column referenced by the Year in H2
H3:
=SUMIF(Sheet1!$A$1:$A$7,Sheet2!$G$3,INDEX(Sheet1!$ A$1:$J$1,1,MATCH(Sheet2!H$2,Sheet1!$A$1:$J$1,0)))

Copy H3 to I3 to harvest the sum for the Year in cell I2

Adjust references to suit your situation.
Post back with any questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I am trying to create a formula that will look for a value on another
worksheet and return a number of values from the same row of each
instance it finds....

The worksheet I am linking to is formatted as follows...

Country Provider Technology Definition Cost 2004 2005 2006 2007 2008
UK A
France A
Germany B
UK C
Spain A
UK C

I want to lookup all instances of a country I input in cell G3 on a
different worksheet, and have it return the data from the other columns
for each instance of that country that it finds. Ideally I would like
it to return the column value based on the title of each column, but
that's a separate problem.

At the moment I am using the following array formula, but it is
returning a NUM# error.

{=INDEX("'"&$G$3&"'!"&A$1:BC$100,IF("'"&$G$3&"'!"& $A$1:$A$100=$G$2,ROW($A$1:$A$100),""),
COLUMN(B1)}

Any advice would be greatly appreciated.

Many thanks

Andrew