+ Reply to Thread
Results 1 to 6 of 6

Array formula issue

  1. #1
    Registered User
    Join Date
    04-23-2018
    Location
    Adelaide
    MS-Off Ver
    2013
    Posts
    3

    Post Array formula issue

    excel error.PNG

    Hi All,

    I am having the above issue and can't seem to figure out why.

    As per the photo, when I use that offset formula, it provides me with a #Value error. However, if i debug that part using F9 then the formula works fine.

    Appreciate your help.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: Array formula issue

    Hi ucj8b. Welcome to the forum.

    I have some ideas. Without context (a sample Excel file upload ... not screenshots or pics; saves retyping data) I have to guess a lot.

    If you are not familiar with how to do this:

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    04-23-2018
    Location
    Adelaide
    MS-Off Ver
    2013
    Posts
    3

    Re: Array formula issue

    Hi FlameRetired,

    Thanks for the welcome and instructions to upload. Please find attached the file (I've desensitized the data and cleaned out unnecessary info)
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: Array formula issue

    Some functions and formula results (usually arrays) that are "resistant". This frequently occurs with OFFSET returns. Try changing formula in F7 to

    =INDEX(Data!$C$2:$C$8,MATCH(1,(Data!$A$2:$A$8=$E7)
    *(Data!$B$2:$B$8=T(OFFSET($F7,-1*(ROW($F7)-LARGE(IF(ISNUMBER($B$6:$B7),ROW($B$6:$B7)),1)),,))),0))

    Array entered of course.

    The T and N functions are often all that is needed. If it is an array or range that is returned it may further require T(+array). The same is true on the N function.

    Once in a while you will encounter arrays that are even more resistant. These require extra steps and almost always include and N (or T) function call.

    F8:F9 return errors because there are no matches in 'Data'.

  5. #5
    Registered User
    Join Date
    04-23-2018
    Location
    Adelaide
    MS-Off Ver
    2013
    Posts
    3

    Re: Array formula issue

    Thanks, you lifesaver!

    Adding the T function definitely resolves the issue.

    Still new to using arrays so it's the first time I've come across the T and N function - will definitely look further into it.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: Array formula issue

    You're welcome. Glad to help and thanks for the feedback.

    If you are interested XOR LX has some articles discussing their use. He goes into detail.

    Check out array coercion articles.

    https://excelxor.com/

+ 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] Evaluate Array Formula Issue
    By juantsaplenty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2017, 12:32 PM
  2. Array formula issue:
    By SUPPO_USN in forum Excel General
    Replies: 2
    Last Post: 08-23-2016, 03:12 PM
  3. Array Formula Issue - Need Help
    By igotgame in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2016, 01:14 PM
  4. Array Formula Issue
    By FWM in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2015, 08:57 AM
  5. [SOLVED] Issue with formula array
    By purlo in forum Excel General
    Replies: 9
    Last Post: 06-08-2015, 03:26 PM
  6. Array formula issue
    By dushtin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2013, 11:53 AM
  7. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 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