+ Reply to Thread
Results 1 to 7 of 7

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

  1. 03-17-2014, 03:06 PM #1
    ahenobarb
    ahenobarb is offline
    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.
    Reply With QuoteRegister To Reply
  2. 03-17-2014, 03:13 PM #2
    XOR LX
    XOR LX is offline
    Forum Expert 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/
    Reply With QuoteRegister To Reply
  3. 03-17-2014, 03:33 PM #3
    ahenobarb
    ahenobarb is offline
    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.
    Reply With QuoteRegister To Reply
  4. 03-17-2014, 03:37 PM #4
    XOR LX
    XOR LX is offline
    Forum Expert 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
    Reply With QuoteRegister To Reply
  5. 03-17-2014, 03:46 PM #5
    ahenobarb
    ahenobarb is offline
    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.
    Reply With QuoteRegister To Reply
  6. 03-17-2014, 03:50 PM #6
    XOR LX
    XOR LX is offline
    Forum Expert 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
    Reply With QuoteRegister To Reply
  7. 03-17-2014, 08:10 PM #7
    newdoverman
    newdoverman is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail
    By sfike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. Index, match, dynamic and connected without sorting helper column non-adjacent index match
    By Underling in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  3. Lookup or Index(match) function not in ascending order and not an exact match
    By Tobias1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  4. [SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings
    By bkwins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Index/Match/Match. Formula looking veritcal and horizontal headings to find a match
    By rain4u in forum Excel Programming / VBA / Macros
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1