How can I return text using an array formula

Posted on 2012-08-31
Last Modified: 2012-08-31
I have a worksheet that is working great but in column "T" of worksheet "Nambucca_October 2011" the array formula returns a "Blank" where it should return "Text". The same code works in other colored cells in row (5) but not in Column "T", the macro places the formula in the colored cells.
I would like the code to return a "Blank" only if no data is available. In this test worksheet I have added data for testing. The worksheet "HardCopyOfMacro" shows the code with the line of code causing the problem "highlighted".
Can someone identify any issues and fix them.

Question by:user2073
    LVL 18

    Expert Comment


    This works for me.

    =IFERROR(INDEX('ws1'!$A$4:$V$10000,MATCH(A5&TEXT(EDATE($B$2,-1),"mmmmyyyy"),'ws1'!$D$4:$D$10000 & 'ws1'!$C$4:$C$10000 & 'ws1'!$B$4:$B$10000,0), MATCH("Results2", 'ws1'!$A$2:$V$2,0)),"")

    Array entered. Just remove the division part.


    Author Comment

    This code works a treat but returns "0" if cells are BLANK or no data available.
    Is it possible to return a "BLANK" cell if no data available.

    Also, can you explain "(1/ (1/" in plan english please.

    LVL 18

    Accepted Solution

    Select the range and custom format.

    Format cells > Custom > Type: [=0]"";General > OK

    (1/(1/ creates #DIV/0! error if no values found.

    But when return value is text it always generates the #DIV/0! error, so it displays blank.


    Author Closing Comment

    The talent by you guys is fantastic, you continually inspire me and show totally new areas to look at. The format changes work for me thanks.


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now