+ Reply to Thread
Results 1 to 7 of 7

Use Index:Match function to match a number to the row heading

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2021
    Posts
    17

    Use Index:Match function to match a number to the row heading

    This is probably easy, but its confounding me.

    I'm using the LARGE function to get the largest values in a column of an array and place them in an ordered column on another worksheet. What I need to do after I have these values, is match the row heading to the values obtained from the LARGE function. I'm trying to use Index:Match for this, but my results are always "#N/A".

    I assume this is because the numbers I am using for the LARGE function are based off a calculation and the actual values go out to some power of ten past the decimal point.

    At any rate, I'm stumped.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Use Index:Match function to match a number to the row heading

    Hi,

    It will be difficult to help you without seeing an actual workbook, so if you could upload one that would greatly help your cause.

    Obviously replace any confidential/sensitive information with dummy data if necessary.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    04-10-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2021
    Posts
    17

    Re: Use Index:Match function to match a number to the row heading

    sample - Copy.xlsx

    ... for example.

    It works if you do the Index:Match with the numbers in the percentage column, but not the converted numbers. It must have to do with the calculation.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Use Index:Match function to match a number to the row heading

    Are you sure your lookup_array for the MATCH shouldn't be column D?

    Regards

  5. #5
    Registered User
    Join Date
    04-10-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2021
    Posts
    17

    Re: Use Index:Match function to match a number to the row heading

    Yes, it's column D. I tested it again using column "C" to establish that it works with the % (which is not in my data), but not with column "D". I may have forgotten to switch the formula back, but as you see it still doesn't work with column D.

    UPDATE
    UGH! It was a merged cell that kept throwing the ranges off in the original spreadsheet. Well, that plus the need to manually press "calculate now". Working now. Jees, I need to take an excel break!
    Last edited by ahenobarb; 03-17-2014 at 03:53 PM.

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Use Index:Match function to match a number to the row heading

    Sorry - I'm completely lost.

    If I change your formula in B2 of that attachment to:

    =INDEX(Sheet1!A$2:A$20,MATCH(Sheet2!C2,Sheet1!D$2:D$20,0))

    then everything works fine.

    Can you please clarify?

    Regards

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Use Index:Match function to match a number to the row heading

    This seems to work correctly with your sample file:...oops! Same solution as already posted by XOR LX.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  3. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Replies: 5
    Last Post: 02-29-2012, 08:51 PM

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