+ Reply to Thread
Results 1 to 4 of 4

Adding error handling into an array formula that finds row numbers of a matching value

  1. #1
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    185

    Adding error handling into an array formula that finds row numbers of a matching value

    Hi,

    I need to have a cell on a worksheet where a value can be entered to check if it exists in column A. I need to then return all the row numbers where that value exists, it may be there multiple times and if so I need to list all the row numbers.

    So far I have used this array formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copied down a number of rows is does return the results I want, but then also returns the #NUM! error for additional rows where there are no more of the value I am checking for. I want the additional rows to remain blank and am struggling with this part. If the value entered to check doesn't exist at all, then all rows should remain blank.

    Please can someone help with modifying this formula appropriately.

    I have attached a small example which hopefully makes it clear what I mean.

    Many thanks
    Attached Files Attached Files
    Last edited by D.Lovell; 11-03-2014 at 04:20 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Adding error handling into an array formula that finds row numbers of a matching value

    Just add: =IFERROR(YourFormula,"")

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Homeless. I am not kidding.
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,134

    Re: Adding error handling into an array formula that finds row numbers of a matching value

    add IFERROR formula? =IFERROR(SMALL(IF($A$2:$A$8=$I$2,ROW($A$2:$A$8)),ROW(1:1)),"")

  4. #4
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    185

    Re: Adding error handling into an array formula that finds row numbers of a matching value

    Cool thanks

+ 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. Replies: 1
    Last Post: 02-01-2013, 06:30 PM
  2. [SOLVED] matching index of an array - works for numbers not letters
    By gryffin13 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2012, 10:43 AM
  3. [SOLVED] Loop Until it finds matching string
    By Hallet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2012, 03:02 PM
  4. Handling error in formula with blank cell?
    By Chantel1326 in forum Excel General
    Replies: 3
    Last Post: 10-09-2008, 08:02 PM
  5. Error handling with a handling routine
    By ben in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2005, 11:06 AM

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