+ Reply to Thread
Results 1 to 10 of 10

Index, Match, Dynamic column doesn't work

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Index, Match, Dynamic column doesn't work

    Hi - I want to do an index-match based upon a value in an another table.

    this works:
    =INDEX('New Player Avgs (2)'!$FG$5:$FK$111,MATCH("olsen",'New Player Avgs (2)'!$FG5:$FG100,0),5) (yields .682)

    This does not:
    =INDEX('New Player Avgs (2)'!$FG$5:$FK$111,MATCH("olsen",'New Player Avgs (2)'!$FG:$FG,0),5) (yields .000)

    My goal is to have a dynamic row count based upon column FG.

    What am I doing wrong.?

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,441

    Re: Index, Match, Dynamic column doesn't work

    Hi there,

    Please Login or Register  to view this content.
    This refers to a Range which begins on Row 5

    This identifies the first instance of "olsen" which occurs in a range beginning on Row 1

    If the first instance of "olsen" occurs on Row (e.g.) 6, the MATCH function will return a value of 6, but the INDEX function will return the sixth row of the Range which begins on Row 5, i.e. Row 10.


    Hope this helps.

    Regards,

    Greg M
    Last edited by Greg M; 11-14-2019 at 09:40 PM. Reason: Typo corrected

  3. #3
    Registered User
    Join Date
    03-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Index, Match, Dynamic column doesn't work

    Hi Greg,
    'olsen' is at cell FG26. FG26 = .682.

    Still confused.
    Chuck
    Last edited by chuckf201; 11-16-2019 at 10:35 AM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,081

    Re: Index, Match, Dynamic column doesn't work

    no sample file

    Please Login or Register  to view this content.
    Ben Van Johnson

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,247

    Re: Index, Match, Dynamic column doesn't work

    Administrative note
    Your profile indicates XL2003. Is this still the case? If not please update your profile

    Read our magnificent banner to attach a sheet
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  6. #6
    Registered User
    Join Date
    03-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Index, Match, Dynamic column doesn't work

    Hi protonLeah,
    You did it. Many thanks. I wouldn't have guessed about counta.
    Why did COUNTA work and FG:FG did not?

    Chuck - from Monrovia, Ca.
    Last edited by chuckf201; 11-16-2019 at 10:38 AM.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,081

    Re: Index, Match, Dynamic column doesn't work

    Quote Originally Posted by Greg M View Post
    Hi there,
    Please Login or Register  to view this content.
    This refers to a Range which begins on Row 5
    This identifies the first instance of "olsen" which occurs in a range beginning on Row 1
    If the first instance of "olsen" occurs on Row (e.g.) 6, the MATCH function will return a value of 6, but the INDEX function will return the sixth row of the Range which begins on Row 5, i.e. Row 10.


    Using FG:FG for the match, if there is a match above row 5 (the first row of the INDEX range), then the INDEX function will return the value from column FK which is obviously 0.
    To create a dynamic range starting at row 5, and ending at the row corresponding to the number of values in column FG, I used:
    'New Player Avgs (2)'!$FG5:INDEX('New Player Avgs (2)'!FG:FG,COUNTA('New Player Avgs (2)'!FG:FG))
    **Note: if there are values in FG above row 5 the bottom of the range will be off by the number of items above row 5 and you will have to modify the formula to:
    ...COUNTA('New Player Avgs (2)'!FG:FG) + x...
    x - the number of used rows above FG5.

  8. #8
    Registered User
    Join Date
    03-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Index, Match, Dynamic column doesn't work

    Can COUNTA also work with the 'index' part of the formula to make it truly row independent?

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,081

    Re: Index, Match, Dynamic column doesn't work

    if you mean to replace the MATCH portion, then no. In this case it only tells you the number of used rows in a column, not the row of a particular item. One alternative is to create a dynamic named range, say, Player_Avg, with:
    Please Login or Register  to view this content.
    Then the cell formula will be simpler:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Index, Match, Dynamic column doesn't work

    WOW - that makes life so much easier.
    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. INDEX MATCH MATCH doesn't work well in a Filtered List?
    By exceln3wb in forum Excel General
    Replies: 1
    Last Post: 10-05-2017, 06:57 AM
  2. Match Index Doesn't Work
    By benjie1984 in forum Excel General
    Replies: 3
    Last Post: 03-02-2017, 04:32 PM
  3. [SOLVED] Index exact match doesn't work
    By I.am.Rustam in forum Excel General
    Replies: 2
    Last Post: 11-11-2015, 11:55 PM
  4. function INDEX MATCH MATCH doesn´t work
    By leonelcd in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2015, 11:36 AM
  5. Index + Match doesn't work with text
    By blueturnaround in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-19-2014, 01:18 PM
  6. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  7. Replies: 6
    Last Post: 11-08-2013, 10:29 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1