+ Reply to Thread
Results 1 to 6 of 6

Index match giving #N/A

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    Posts
    285

    Index match giving #N/A

    Hi,

    As you can see from the attached file Ive an index match issue. Basically when one of my lookup values is repeated in the lookup column it returns an #N/A! error.

    In the attached:

    Look up ID: 2012
    Look up Week: 2
    Query Returns: 500 (CORRECT!)

    Look up ID: 2012
    Look up Week: 4
    Query Returns: #N/A! (BOOO!!)

    Im guessing it's limitation of Indexmatch? Although im not sure...........Anyone any ideas as to the issue/alternative?
    Attached Files Attached Files
    Last edited by Barking_Mad; 10-27-2020 at 11:16 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,469

    Re: Index match giving #N/A

    This will work, however not for your second example, because there is no week 3!!! Try 4 instead.

    =LOOKUP(2,1/((test!$A$2:$A$12=C2)*(test!$B$2:$B$12=C3)),test!$C$2:$C$12)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: Index match giving #N/A

    Hi,
    Same formula entered as array.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click just below left if it helps, Booşathì

  4. #4
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    Posts
    285

    Re: Index match giving #N/A

    Quote Originally Posted by AliGW View Post
    This will work, however not for your second example, because there is no week 3!!! Try 4 instead.

    =LOOKUP(2,1/((test!$A$2:$A$12=C2)*(test!$B$2:$B$12=C3)),test!$C$2:$C$12)
    Hah, thanks yes that was a typo, although I did amend, but not quick enough!

    Is the previous issue because it's looking up the same value in the same column but stops when it finds the first instance?

    Ill have a go with your suggestion. thanks

    Edit: I did get it working with sumproduct but in the actual sheet this was really slow....

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,469

    Re: Index match giving #N/A

    Yes, and had you entered it correctly (see post #3), it would have worked.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Edit: I did get it working with sumproduct but in the actual sheet this was really slow....
    Using full column references, perhaps? If so, limit them to just what is needed.

  6. #6
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    Posts
    285

    Re: Index match giving #N/A

    Thanks. staring at formulas is making me go goggle eyed!

+ 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. [SOLVED] Index-Match array giving first value when no match
    By BPSJACK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2019, 09:30 AM
  2. [SOLVED] Index Match Multiple criteia Giving zero value
    By lauren11 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-04-2019, 01:15 AM
  3. [SOLVED] Index Match across two sheets giving unexpected results
    By Sprong in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-30-2019, 04:02 PM
  4. Index/match - giving #NA but value is correct
    By MartinGTC in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-12-2017, 12:49 PM
  5. [SOLVED] Index - Match formula not giving me an expected result
    By longbow007 in forum Excel General
    Replies: 5
    Last Post: 11-25-2015, 07:34 PM
  6. INDEX/MATCH with =LARGE is giving duplicates
    By keith740 in forum Excel General
    Replies: 6
    Last Post: 10-07-2015, 03:35 AM
  7. [SOLVED] Index, Match and Indirect giving REF error
    By stanleds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2013, 06:08 AM

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