Index headache

I'm using the folowing formula to locate a specific cell
and return the value in that cell based on matching 2
different values to values in cells to the side of and
above the specific value I want (I'm using the values in
two columns as x y coordinates to locate and enter a
value in a cell automatically.

=INDEX(Sheet1!$B$1:$AE$100,MATCH(A31,Sheet1!
$B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0))
Unfortunately this always give me the value one cell left
of the value I want.

Any ideas

thanks in advance

ps. the numbers used s coordinates are in columns a ,b
sheet 1 has the values i need.
the range of values to be matched with column b is sheet
1 c1:ae1 (x coord)
The range of values to be matched with column a is sheet
1 b1:b100 (y coord) ( which seems to be working fine.

Hi

personally, i'ld use the offset function
=OFFSET(Sheet1!$B$1,MATCH(A31,Sheet1!
$B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0))

but if you really want to use the index function change it to
=INDEX(Sheet1!$C$2:$AE$100,MATCH(A31,Sheet1!
$B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0))
i.e. start it in the result area not the title area

let us know how you go

Cheers
JulieD




wrote in message
...
I'm using the folowing formula to locate a specific cell
and return the value in that cell based on matching 2
different values to values in cells to the side of and
above the specific value I want (I'm using the values in
two columns as x y coordinates to locate and enter a
value in a cell automatically.

=INDEX(Sheet1!$B$1:$AE$100,MATCH(A31,Sheet1!
$B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0))
Unfortunately this always give me the value one cell left
of the value I want.

Any ideas

thanks in advance

ps. the numbers used s coordinates are in columns a ,b
sheet 1 has the values i need.
the range of values to be matched with column b is sheet
1 c1:ae1 (x coord)
The range of values to be matched with column a is sheet
1 b1:b100 (y coord) ( which seems to be working fine.

Probably use
MATCH(B31,Sheet1!$B$1:$AE$1,0)
instead of MATCH(B31,Sheet1!$C$1:$AE$1,0))
Replace math array of $C$1:$ae$1 to $b$1:$AE$1 as you have
indexed $B1:$AE100

-----Original Message-----
I'm using the folowing formula to locate a specific cell
and return the value in that cell based on matching 2
different values to values in cells to the side of and
above the specific value I want (I'm using the values in
two columns as x y coordinates to locate and enter a
value in a cell automatically.

=INDEX(Sheet1!$B$1:$AE$100,MATCH(A31,Sheet1!
$B$1:$B$100,0),MATCH(B31,Sheet1!$C$1:$AE$1,0))
Unfortunately this always give me the value one cell left
of the value I want.

Any ideas

thanks in advance

ps. the numbers used s coordinates are in columns a ,b
sheet 1 has the values i need.
the range of values to be matched with column b is sheet
1 c1:ae1 (x coord)
The range of values to be matched with column a is sheet
1 b1:b100 (y coord) ( which seems to be working fine.
.