+ Reply to Thread
Results 1 to 4 of 4

If error won't return blanks

  1. #1
    Registered User
    Join Date
    09-20-2018
    Location
    Bradford
    MS-Off Ver
    2010
    Posts
    15

    Exclamation If error won't return blanks

    So i'm trying to get the formula below to show blanks instead of #N/A and REFS and for the life of me it keeps saying I'm missing parenthesis or there's not enough arguments. Can someone please point out the obvious for me?

    =INDEX('[AFP Template Proposition.xlsx]Data'!$W$2:$W$2912, MATCH(1, INDEX((B162='[AFP Template Proposition.xlsx]Data'!$B$2:$B$2912) * ($K$4='[AFP Template Proposition.xlsx]Data'!$R$2:$R$2912), 0, 1), 0))

    So I'm hoping where it says Column K it will show as a 1 or blank and I have to apply the logic to the subsequent columns
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: If error won't return blanks

    use IFERROR( formula , "")


    =IFERROR(INDEX('https://gallagherltd-my.sharepoint.com/personal/nicky_nel_networkplus_co_uk/Documents/[AFP Template Proposition.xlsx]Data'!$W$2:$W$2912, MATCH(1, INDEX((B162='https://gallagherltd-my.sharepoint.com/personal/nicky_nel_networkplus_co_uk/Documents/[AFP Template Proposition.xlsx]Data'!$B$2:$B$2912) * ($K$4='https://gallagherltd-my.sharepoint.com/personal/nicky_nel_networkplus_co_uk/Documents/[AFP Template Proposition.xlsx]Data'!$R$2:$R$2912), 0, 1), 0)),"")

    cant use your formula as its using loads of external reference

    BUT
    IFERROR worked on K162
    Attached Files Attached Files
    Last edited by etaf; 01-07-2022 at 02:17 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-20-2018
    Location
    Bradford
    MS-Off Ver
    2010
    Posts
    15

    Re: If error won't return blanks

    Hi there,


    Didn't realise I hadn't responded. Thank you for your help on this!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: If error won't return blanks

    you are welcome

+ 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 Return 1st, 2nd,3rd Number in a Column Containing Blanks?
    By finalmike in forum Excel General
    Replies: 9
    Last Post: 08-09-2018, 11:00 AM
  2. [SOLVED] Look for blanks & return the person's name on a sheet
    By Jamesraywebber85 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-12-2018, 02:32 PM
  3. VBA return non blanks
    By hector.rmzv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2016, 05:00 PM
  4. How to return blanks in Excel?
    By BillJo in forum Excel General
    Replies: 2
    Last Post: 03-17-2015, 04:59 PM
  5. [SOLVED] Array Formula - Return blanks instead of zero
    By rogelioperrett in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 10-15-2014, 10:00 AM
  6. vlookup to return 0 and blanks
    By jvbeats in forum Excel General
    Replies: 5
    Last Post: 03-04-2013, 12:45 PM
  7. How to return blanks for $0.00 cells?
    By DrMutley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2007, 07:33 PM

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