# Array formula issue

1. ## 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.

2. ## 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”.
• click “Close window”

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)

4. ## 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. ## 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. ## 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/

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