Close

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 88
  1. #26
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Im pretty sure it will be a LOOKUP function. But I havent done that in years

  2. #27
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    They are in alphabetical order so a straight LOOKUP function will work.

    In your case CELL K6 will be =LOOKUP($A6,$N$6:$N$2000,$S6:$S2000).
    Last edited by Narkee; 28th October 2015 at 05:58 PM. Reason: FORMULA UPDATED

  3. #28
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    oh wait, just read your comment under pic. Let me edit the formula.

  4. #29
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma

  5. #30
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace

    MS Excel Help Thread

    Quote Originally Posted by sblack View Post
    Yes its Fantasy Football spreadsheet




    I essentially need the value in the second salary column (S) to be put into the corresponding cell in Column K, as long as the name matches. Which as you can see doesnt match up exactly in each row.
    Do the names in column N match exactly those in column A?

    Or do they have the name without the (MIN - RB) at the end?
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  6. #31
    #demteeth origi's Avatar
    Join Date
    Oct 2014
    Posts
    3,812
    Rep Level
    5259
    Rep Power
    209
    WCE Liverpool
    Index match. Lookups are for plebs

  7. #32
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace
    Quote Originally Posted by origi View Post
    Index match. Lookups are for plebs
    never heard of it and just read it up
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  8. #33
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    Quote Originally Posted by origi View Post
    Index match. Lookups are for plebs
    Better for when data isn't organised alphabetically. Otherwise it just creates unnecessarily big files.

    edit - I do love index match tho.
    Last edited by Narkee; 28th October 2015 at 09:01 PM.

  9. #34
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Nah theyre all the same. Ive removed the stuff in brackets at the end.

    aware on index

  10. #35
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace
    Quote Originally Posted by sblack View Post
    Nah theyre all the same. Ive removed the stuff in brackets at the end.

    aware on index
    =VLOOKUP(A6,N:S,6,)
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  11. #36
    Veteran yeah_nah's Avatar
    Join Date
    Oct 2014
    Location
    Perth
    Teams
    Toasters
    Posts
    9,838
    Rep Level
    22944
    Rep Power
    627
    Narkee i dont understand why you rave about lookup so much? Whys it better than v lookup?
    Quote Originally Posted by Eagle E View Post
    I then started saving extra money as a result of eating dumpster food.

    I knew when to hit the dumpster for optimum catch.
    Quote Originally Posted by Eagle E View Post
    accualy scared of getting knocked out by yenny
    #broughtback

  12. #37
    #demteeth origi's Avatar
    Join Date
    Oct 2014
    Posts
    3,812
    Rep Level
    5259
    Rep Power
    209
    WCE Liverpool
    Quote Originally Posted by showdownhero View Post
    =VLOOKUP(A6,N:S,6,)
    If this is correct.

    =INDEX(S:S,MATCH(A6,N:N),0)

  13. #38
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    Quote Originally Posted by yeah_nah View Post
    Narkee i dont understand why you rave about lookup so much? Whys it better than v lookup?
    They are both fine. I never used to like vlookup until I discovered the column function. My knock on vlookup was always the fact that you had to hardcode the column reference number. This takes away from model integrity because if you were to insert another column in the array then it would be referencing the incorrect column. With the column function you can go column(reference)-column(reference)+1 (where the first column ref references the column the result data is in and the second column ref references the first column in the array) and you will never have an issue with inserting columns.

    Vlookup is actually much more powerful that lookup because you don't need to have your lookup data in numerical or alphabetical order.

    Index match is great for extracting specific data where columns and rows require referencing. The only knock I have on it is if the array you are looking at is large and you have plenty of index match functions, your model become huge.

  14. #39
    Veteran yeah_nah's Avatar
    Join Date
    Oct 2014
    Location
    Perth
    Teams
    Toasters
    Posts
    9,838
    Rep Level
    22944
    Rep Power
    627
    Yeh ive got some index match formulas in a monster spreadsheet and its a fucking prick of a thing hooked in with an ODBC of a fucktonne of data

    Was unaware of the requirement to have shit in order for lookup, makes sense why I could never get it to work though

    Can't say I've had too much of an issue with the reference number but the column trick is a handy one

    Quote Originally Posted by Eagle E View Post
    I then started saving extra money as a result of eating dumpster food.

    I knew when to hit the dumpster for optimum catch.
    Quote Originally Posted by Eagle E View Post
    accualy scared of getting knocked out by yenny
    #broughtback

  15. #40
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Anyone know macros?

  16. #41
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace
    Quote Originally Posted by sblack View Post
    Anyone know macros?
    kinda
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  17. #42
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Im guessing its possible

    I essentially want it to auto fill a set criteria ie a team, under a salary cap and maximising points

    Lineup chooser.

    Im guessing it would be pretty advanced

  18. #43
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace
    Quote Originally Posted by sblack View Post
    Im guessing its possible

    I essentially want it to auto fill a set criteria ie a team, under a salary cap and maximising points

    Lineup chooser.

    Im guessing it would be pretty advanced
    Pretty sure that would be best done using filters rather than macros.
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  19. #44
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Quote Originally Posted by showdownhero View Post
    Pretty sure that would be best done using filters rather than macros.
    Hmmmm maybe. I think it might be too complex for filters though

  20. #45
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace
    Quote Originally Posted by sblack View Post
    Hmmmm maybe. I think it might be too complex for filters though
    if you send me the file I can have a look at it for you
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  21. #46
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Quote Originally Posted by showdownhero View Post
    if you send me the file I can have a look at it for you
    Working on the full database. Will probably finish it on Friday

  22. #47
    crem de la cra showdownhero's Avatar
    Join Date
    Oct 2014
    Location
    Melbourne
    Teams
    Adelaide, Crystal Palace
    Posts
    10,181
    Rep Level
    19117
    Rep Power
    603
    Adelaide Crows Crystal Palace
    Quote Originally Posted by sblack View Post
    Working on the full database. Will probably finish it on Friday
    NFL fantasy right? So you will need certain positions and under a certain salary cap?
    Quote Originally Posted by Gamblor View Post
    You're being a massive Clint

  23. #48
    #demteeth origi's Avatar
    Join Date
    Oct 2014
    Posts
    3,812
    Rep Level
    5259
    Rep Power
    209
    WCE Liverpool
    Lookups can only look one way, can be broken if you insert columns / rows. Index match can also look up multiple criteria.

  24. #49
    Veteran yeah_nah's Avatar
    Join Date
    Oct 2014
    Location
    Perth
    Teams
    Toasters
    Posts
    9,838
    Rep Level
    22944
    Rep Power
    627
    Quote Originally Posted by origi View Post
    Lookups can only look one way, can be broken if you insert columns / rows. Index match can also look up multiple criteria.
    Aware they can go only one way but my question was on why narkee thought lookup was superior over v lookup, not why index match is superior m8
    Quote Originally Posted by Eagle E View Post
    I then started saving extra money as a result of eating dumpster food.

    I knew when to hit the dumpster for optimum catch.
    Quote Originally Posted by Eagle E View Post
    accualy scared of getting knocked out by yenny
    #broughtback

  25. #50
    #demteeth origi's Avatar
    Join Date
    Oct 2014
    Posts
    3,812
    Rep Level
    5259
    Rep Power
    209
    WCE Liverpool
    Offers far more flexibility

Tags for this Thread

Posting Permissions

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