Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Lookups

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin -

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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Iain -

    Thanks.

    About to give it a try

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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
    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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.


  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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}

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •