Look up Function Need
 

Hi

On one sheet i have a list of all bowlers and their average. Column A is
the Name and Column B - E is there Average for four weeks. Each week varying.

On a 2nd Sheet Is follows
A B
Week 2
Greg Hill

in B2 i need a Formula to look up the bowlers name in Cell A2 and return
their average Week 2 (Cell A1).

I am no expert in Excel but know my way around a little. Is this possible
and how do i do it.

Thanks Heaps

Assuming that the cell A1 contains the tesxt Week and a s[ace, then use

=INDEX(Sheet2!A1:E20,MATCH(A2,Sheet2!A1:A20,0),RIG HT(A1)+1)

If you just input the week number, just use

=INDEX(Sheet2!A1:E20,MATCH(A2,Sheet2!A1:A20,0),A)

--
HTH

Bob Phillips

"Andrew C" wrote in message
...
Hi

On one sheet i have a list of all bowlers and their average. Column A is
the Name and Column B - E is there Average for four weeks. Each week
varying.

On a 2nd Sheet Is follows
A B
Week 2
Greg Hill

in B2 i need a Formula to look up the bowlers name in Cell A2 and return
their average Week 2 (Cell A1).

I am no expert in Excel but know my way around a little. Is this possible
and how do i do it.

Thanks Heaps

Thanks it works ok. For one of my sheets which is good.

I have another sheets with 46 Weeks and when i get past week 9 it goers back
to the start again. What do i need to adjust in the formula or do i need a
new formula.

Thanks



"Bob Phillips" wrote:

Assuming that the cell A1 contains the tesxt Week and a s[ace, then use

=INDEX(Sheet2!A1:E20,MATCH(A2,Sheet2!A1:A20,0),RIG HT(A1)+1)

If you just input the week number, just use

=INDEX(Sheet2!A1:E20,MATCH(A2,Sheet2!A1:A20,0),A)

--
HTH

Bob Phillips

"Andrew C" wrote in message
...
Hi

On one sheet i have a list of all bowlers and their average. Column A is
the Name and Column B - E is there Average for four weeks. Each week
varying.

On a 2nd Sheet Is follows
A B
Week 2
Greg Hill

in B2 i need a Formula to look up the bowlers name in Cell A2 and return
their average Week 2 (Cell A1).

I am no expert in Excel but know my way around a little. Is this possible
and how do i do it.

Thanks Heaps

Andrew,

Try

RIGHT(A1,LEN(A1)-FIND(" ",A1))+1

instead of

RIGHT(A1)+1

in the formula

--
HTH

Bob Phillips

"Andrew C" wrote in message
...
Thanks it works ok. For one of my sheets which is good.

I have another sheets with 46 Weeks and when i get past week 9 it goers
back
to the start again. What do i need to adjust in the formula or do i need
a
new formula.

Thanks



"Bob Phillips" wrote:

Assuming that the cell A1 contains the tesxt Week and a s[ace, then use

=INDEX(Sheet2!A1:E20,MATCH(A2,Sheet2!A1:A20,0),RIG HT(A1)+1)

If you just input the week number, just use

=INDEX(Sheet2!A1:E20,MATCH(A2,Sheet2!A1:A20,0),A)

--
HTH

Bob Phillips

"Andrew C" wrote in message
...
Hi

On one sheet i have a list of all bowlers and their average. Column A
is
the Name and Column B - E is there Average for four weeks. Each week
varying.

On a 2nd Sheet Is follows
A B
Week 2
Greg Hill

in B2 i need a Formula to look up the bowlers name in Cell A2 and
return
their average Week 2 (Cell A1).

I am no expert in Excel but know my way around a little. Is this
possible
and how do i do it.

Thanks Heaps