Lookups

[Merlin]

Can anyone advise on how to do a lookup that picks up the previous value in a column in a table ?

The value will be repeated several times in the table and I want to refer to the last occurrence. The table is not sorted and the most recent data is added to the bottom of the spreadsheet.

All the lookup funstions seem to pick up the first instance of the data - how do I get a reference to the last ?

Thanks

 

[Aladin Akyurek]

On 2002-04-29 03:09, Merlin wrote:
Can anyone advise on how to do a lookup that picks up the previous value in a column in a table ?

The value will be repeated several times in the table and I want to refer to the last occurrence. The table is not sorted and the most recent data is added to the bottom of the spreadsheet.

All the lookup funstions seem to pick up the first instance of the data - how do I get a reference to the last ?

Thanks

Care to provide a small sample of your data along with the expected result?
This message was edited by Aladin Akyurek on 2002-04-29 03:14

 

[Merlin]

Aladin -

I posted my data requirment, but I can't see where it went ?

 

[Aladin Akyurek]

On 2002-04-29 03:35, Merlin wrote:
Aladin -

I posted my data requirment, but I can't see where it went ?

Well, try again.

 

[Iain Lewis]

Try this formula :

=INDIRECT("a"&MATCH(c1,A2:A11,1)-1)

This assumes :
C1 contains the value which you want to match
The lookup range is A2 to A11

 

[Merlin]

I'm trying to keep records for an archery club with the members' handicaps changing after every round that they shoot. The dat looks a bit like:

Name Round Date Handicap Score New H/cap

Smith FITA 05/02 35 925 33
Jones FITA 05/02 42 868 41
Brown Bray 15/02 48 246 49
Smith York 04/03 33 852 34
Brown York 04/03 49 775 48
Jones York 04/03 49 782 47
Smith Bray 15/03 33 275 32

I want the round that Smith shot on the 15/03 to refer back to the handicap held on 04/03 and not round shot on the 05/02. Every lookup that I've tried relates to the first instance in the list, not the previous occurrence.

Thanks

 

[Merlin]

Iain -

Thanks.

About to give it a try

 

[Aladin Akyurek]

On 2002-04-29 03:52, Merlin wrote:
I'm trying to keep records for an archery club with the members' handicaps changing after every round that they shoot. The dat looks a bit like:

Name Round Date Handicap Score New H/cap

Smith FITA 05/02 35 925 33
Jones FITA 05/02 42 868 41
Brown Bray 15/02 48 246 49
Smith York 04/03 33 852 34
Brown York 04/03 49 775 48
Jones York 04/03 49 782 47
Smith Bray 15/03 33 275 32

I want the round that Smith shot on the 15/03 to refer back to the handicap held on 04/03 and not round shot on the 05/02. Every lookup that I've tried relates to the first instance in the list, not the previous occurrence.

Thanks

Assume your sample data to be in A1:F8.

{"Name","Round","Date","Handicap","Score","New";
"Smith","FITA",37292,35,925,33;
"Jones","FITA",37292,42,868,41;
"Brown","Bray",37302,48,246,49;
"Smith","York",37319,33,852,34;
"Brown","York",37319,49,775,48;
"Jones","York",37319,49,782,47;
"Smith","Bray",37330,33,275,32}

In H2 enter: Smith [ your criterion player ]
In I1 enter: 15-Mar [ last shot date ]

The problem statement. Retrieve Smith's data on the last handicap before the one on 15-Mar.

In J1 enter:

=SUMPRODUCT(MAX(($A$2:$A$8=H2)*($C$2:$C$8<I2)*(ROW($A$2:$A$8))))

The above formula computes the row number of the row at which to look for Smith's data.

You can use the result to retrieve any piece of data related to the event. For example, to get the Round, simply use:

=INDEX(B:B,J2)


Note. If your data area is changing frequently, I'd suggest using dynamic name ranges in the formulas above instead of definite ranges and columns as in INDEX. If interested, just say so.

Aladin

 

[Aladin Akyurek]

On 2002-04-29 03:41, Iain Lewis wrote:
Try this formula :

=INDIRECT("a"&MATCH(c1,A2:A11,1)-1)

This assumes :
C1 contains the value which you want to match
The lookup range is A2 to A11

That won't work with unsorted data & cannot cope with the query that wants the one before the last instance.

 

[Merlin]

Aladin -

Thanks. I must be doing something wrong, because I just get 0 returned (cell F10).

={"Name","Start H/Cap","G/L/J","Round","Date","H/Cap for Round","Score",0,"Club Record","H/cap Shot","Allowance","Adj Score","New H/Cap";"Steve Potts",34,"G","Portsmouth",37353,34,560,0,574,31,889,1449,33;"Sally Potts",52,"L","Portsmouth",37353,52,465,0,562,54,967,1432,53;"Richard Darling",46,"G","Portsmouth",37353,46,505,0,574,46,935,1440,46;"Marilyn Vartoukian",34,"L","Portsmouth",37353,34,562,0,562,30,889,1451,32;"Kevin Ross",41,"G","Bray 1",37353,41,204,0,245,51,1201,1405,46;"John Gatenby",48,"G","Portsmouth",37353,48,460,0,574,54,945,1405,51;"Greg Vartoukian",34,"G","Portsmouth",37353,34,535,0,574,39,889,1424,37;"Andy Talbot",36,"G","Portsmouth",37353,0,542,0,574,37,840,1382,37}