+ Reply to Thread
Results 1 to 3 of 3

Index Match #REF! error after table updates

  1. #1
    Registered User
    Join Date
    10-26-2016
    Location
    Australia
    MS-Off Ver
    excel 2016
    Posts
    46

    Index Match #REF! error after table updates

    Hi,

    I'm having a #REF! error when I refresh my power query table . Colum "J" is searching the table and errors when the table is updated.

    The power query table is on "Data2" worksheet and "Find Me Container" worksheet is where the info transfers to.

    Any idea on a work around?

    Thanks.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Index Match #REF! error after table updates

    Your post was overlooked because you posted it in the Access sub-forum. I have moved it for you. There is no need for the helper column. You can use:

    =IFERROR(INDEX(Data2!B:B,AGGREGATE(15,6,ROW(Containers[CONTAINER NAME])/(IF($D$9="Container",Containers[CONTAINER NAME]<>"",Containers[CONTAINER NAME]=$D$9)),ROWS(A$14:A14))),"")

    I have tweaked the formulae in columns C & D as well.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Index Match #REF! error after table updates

    Duhhh. I must be losing the plot. This is simpler:

    =IFERROR(INDEX(Data2!B:B,AGGREGATE(15,6,ROW(Containers[CONTAINER NAME])/(Containers[CONTAINER NAME]=IF($D$9="Container","*",$D$9)),ROWS(A$14:A14))),"")
    Attached Files Attached Files

+ 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] how to combine sumif with index / match to make formula dynamic for updates
    By ExcellingOcfUpsides in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2019, 06:28 AM
  2. [SOLVED] Index,Match,Match based on date Range - error in some cells
    By jmont in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2019, 04:47 AM
  3. unable to get match property error in userform derived index match
    By alexcrofut in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2015, 09:21 PM
  4. Replies: 3
    Last Post: 05-19-2014, 02:01 PM
  5. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. pivot table error but still updates
    By Hakara in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-19-2012, 01:35 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