I have a large spreadsheet (like the sample below) and i need to be able to
look in the Score(us) column and if a certain number is reached (adding up
the column)then look up the Opponents

i.e
1 Canvey Island Reserves
10 Brentwood Reserves

Date Opponents Result Score(us) Score(them)
24-Aug-85 Canvey Island Reserves Won 3 0
26-Aug-85 east thurrock United Reserves Won 3 1
31-Aug-85 Brentwood Reserves Won 5 1
04-Sep-85 witham town Reserves Won 2 1
07-Sep-85 eton manor Reserves Won 1 0
14-Sep-85 maldon town Reserves Won 3 0
21-Sep-85 eton manor Reserves Draw 2 2
28-Sep-85 Sawbridgeworth Town Reserves Won 6 1

I can get this to work if the number is exsact by using the Lookup function,
but not if the number i lookup falls in the middle.

"Purfleet" wrote:

I have a large spreadsheet (like the sample below) and i need to be able to
look in the Score(us) column and if a certain number is reached (adding up
the column)then look up the Opponents

i.e
1 Canvey Island Reserves
10 Brentwood Reserves

Date Opponents Result Score(us) Score(them)
24-Aug-85 Canvey Island Reserves Won 3 0
26-Aug-85 east thurrock United Reserves Won 3 1
31-Aug-85 Brentwood Reserves Won 5 1
04-Sep-85 witham town Reserves Won 2 1
07-Sep-85 eton manor Reserves Won 1 0
14-Sep-85 maldon town Reserves Won 3 0
21-Sep-85 eton manor Reserves Draw 2 2
28-Sep-85 Sawbridgeworth Town Reserves Won 6 1

I can get this to work if the number is exsact by using the Lookup function,
but not if the number i lookup falls in the middle.

Sorry about submitting a blank, I hit the wrong spot.
I assume from your example you are looking for your score not the opponent.
you say you almost had it workingI assume you
added a helper column (sum scores) and used something like
=lookup(10,sum scores range, Opponents range)
Lookup looks for a value less than or equal to the lookup value
you could play with your equation by adding one cell to your sum score range
at the top and use
=lookup(9.9,sum scores range+, opponents range)
or use
=index(opponents range,match(10,sum scores range,-1))
the -1 makes the match lookfor the smallest number greater than or equal to
your lookup number.

"Purfleet" wrote:

I have a large spreadsheet (like the sample below) and i need to be able to
look in the Score(us) column and if a certain number is reached (adding up
the column)then look up the Opponents

i.e
1 Canvey Island Reserves
10 Brentwood Reserves

Date Opponents Result Score(us) Score(them)
24-Aug-85 Canvey Island Reserves Won 3 0
26-Aug-85 east thurrock United Reserves Won 3 1
31-Aug-85 Brentwood Reserves Won 5 1
04-Sep-85 witham town Reserves Won 2 1
07-Sep-85 eton manor Reserves Won 1 0
14-Sep-85 maldon town Reserves Won 3 0
21-Sep-85 eton manor Reserves Draw 2 2
28-Sep-85 Sawbridgeworth Town Reserves Won 6 1

I can get this to work if the number is exsact by using the Lookup function,
but not if the number i lookup falls in the middle.