+ Reply to Thread
Results 1 to 10 of 10

Index, Match, #REF Error

  1. 01-04-2011, 04:58 PM #1
    karstens
    karstens is offline
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Index, Match, #REF Error

    I have two tabs:
    Tab: Circuit Activity and Tab: Database

    On the Circuit Activity tab, in Y426, I am trying to get a price from the Database tab.

    The database tab uses A1:N39

    So if on the Circuit Activity tab, T426 is VG-NU AND U426 is New Ulm to Owatonna, it should look at the table on the database tab, find where VG-NU (Row 1) and New Ulm to Owatonna (Column A) meet, and return that value.

    I used the ctrl, shift, enter and got the curly brackets, but I still get a #REF error. The following is the code I am using.

    =INDEX(Database!B$2:$N$39,MATCH(T426,Database!$B$1:$N$1,0),MATCH(U426,Database!A$2:$A$39,0))

    Can you tell me what I am doing wrong?
    Thank you!
    Last edited by karstens; 01-04-2011 at 07:13 PM. Reason: Problem solved by WHER!
    Reply With QuoteRegister To Reply
  2. 01-04-2011, 05:10 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index, Match, #REF Error

    The formula looks fine and seems like it should work.

    You don't need to confirm it with CSE keys... it is not an array formula.

    Do you have any REF errors within the table?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 01-04-2011, 05:11 PM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Index, Match, #REF Error

    You have your Row and Column references reversed. You shouldn't need to make it an array formula.
    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
    Reply With QuoteRegister To Reply
  4. 01-04-2011, 05:12 PM #4
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Index, Match, #REF Error

    I think he has it a bit wrong too, what about:
    =INDEX(Database!B$2:$N$39,MATCH(T426,Database!A$2:$A$39,0),MATCH(U426,Database!$B$1:$N$1,0))
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
    Reply With QuoteRegister To Reply
  5. 01-04-2011, 05:12 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: Index, Match, #REF Error

    Try:

    =INDEX(Database!$B$2:$N$39,MATCH(T426,Database!$B$1:$N$1,0),MATCH(U426,Database!$A$2:$A$39,0))


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  6. 01-04-2011, 05:12 PM #6
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Index, Match, #REF Error

    Hey, I'm late to the party!!!
    Reply With QuoteRegister To Reply
  7. 01-04-2011, 05:13 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index, Match, #REF Error

    Good catch Chemist... I think that is right. The row/column arguments should be interchanged.
    Reply With QuoteRegister To Reply
  8. 01-04-2011, 06:14 PM #8
    karstens
    karstens is offline
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index, Match, #REF Error

    Thank you everyone for the suggestions. I am still not able to make it work.

    If I use this suggestion:
    =INDEX(Database!$B$2:$N$39,MATCH(T426,Database!A$2:$A$39,0),MATCH(U426,Database!$B$1:$N$1,0))

    I get #NA

    If I use this suggestion:
    =INDEX(Database!$B$2:$N$39,MATCH(T426,Database!$B$1:$N$1,0),MATCH(U426,Database!$A$2:$A$39,0))

    I get #REF

    In answer to a previous question, No, there are no reference errors on the database tab.
    T426 and U426 are data validation fields to be sure the correct information is entered in there, so they should match the database tab exactly.
    Reply With QuoteRegister To Reply
  9. 01-04-2011, 07:05 PM #9
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Index, Match, #REF Error

    I would suggest trying this formula :
    =INDEX(Database!B$2:$N$39,MATCH(U426,Database!A$2:$A$39,0),MATCH(T426,Database!$B$1:$N$1,0))
    or move the validationlists in T426 and U426 around?
    Reply With QuoteRegister To Reply
  10. 01-04-2011, 07:11 PM #10
    karstens
    karstens is offline
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index, Match, #REF Error

    I could scream!

    Your suggestion works WHER! Thank you SO much.
    I appreciate your expertise!
    Last edited by karstens; 01-04-2011 at 07:14 PM.
    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)

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