+ Reply to Thread
Results 1 to 3 of 3

Simple index match problem

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2002
    Posts
    5

    Simple index match problem

    Hi :)

    I hope you can help me, I am awful when it comes to computers and I just can't seem to solve this little issue I have!

    I have used the formula

    =INDEX(Points!$A$5:Points!$A$43, MATCH(2, Points!$AX$5:Points!$AX$43))

    to return a name from a different sheet. I will try to go into as little detail as possible to keep it simple but basically I need to use the formula in 20 cells but I may not need to fill all of them - some should stay blank as there may be no match(18 for example to match. My problem is that instead of staying blank it will return the name of the nearest result to match, for example it will return match(17. All I need to know is how to keep it blank if it doesn't match exactly!

    I am so sorry if this made no sense, I really try to avoid using computer systems for the reason that I have no idea what I'm doing :P If you don't understand what I mean or need more information I will do my best to explain but it's hard when I have no idea what I'm talking about!

    Thank you so much for your time!
    Last edited by NewbieLee; 10-02-2012 at 03:25 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Simple index match problem

    1. Add a 3rd argument to your MATCH function. 0 means you're looking for an exact match so
    =INDEX(Points!$A$5:Points!$A$43, MATCH(2, Points!$AX$5:Points!$AX$43,0))

    2. When it doesn't find anything, you'll get an error (#N/A) so you probably want to nest it in an IF statement to get rid of that
    =IF(ISNA(MATCH(2, Points!$AX$5:Points!$AX$43,0)),"", INDEX(Points!$A$5:Points!$A$43, MATCH(2, Points!$AX$5:Points!$AX$43,0)))

    3. You don't need to reference the sheet twice in a range. It can be simplified to
    =IF(ISNA(MATCH(2, Points!$AX$5:$AX$43,0)),"", INDEX(Points!$A$5:$A$43, MATCH(2, Points!$AX$5:$AX$43,0)))
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2002
    Posts
    5

    Re: Simple index match problem

    That is perfect, thank you so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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