+ Reply to Thread
Results 1 to 5 of 5

Index Match Function? Ignore blank rows - (rows without values)

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Index Match Function? Ignore blank rows - (rows without values)

    Hi -

    So, I am trying to develop a formula which would display the first 0-10 values in a column on Sheet 1, which have an X next to them in Sheet 2. Problem being, on Sheet 2 there will often be blank rows (those which do not have an X next to them) between those that do have Xs on SHeet 2.

    I've tried using an index/match function... am close... but not there yet.

    See attached sheet.

    Help!

    Jacob



    Index Match Example.xlsx

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Match Function? Ignore blank rows - (rows without values)

    Grr. Merged cells - the work of Satan.. really best avoided.

    Anyhow, rant over. Try this.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Match Function? Ignore blank rows - (rows without values)

    There's a mysterious -5 in the formula. It's there because your first data row (6) has 5 rows before it which need to be accounted for.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Index Match Function? Ignore blank rows - (rows without values)

    =IFERROR(INDEX('Data Entry - Checklist'!$B:$B,SMALL(INDEX(('Data Entry - Checklist'!$C$6:$C$13<>"X")*10^10+ROW('Data Entry - Checklist'!$C$6:$C$13),0),ROW(1:1))),"")

    little modication to glens formula
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Match Function? Ignore blank rows - (rows without values)

    Fair point... and to be honest - it's Siva's formula. He may have copied it from someone else, but I certainly copied it from him!!

+ 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: 0
    Last Post: 11-05-2014, 10:54 AM
  2. [SOLVED] Ignore Blank Values While Using INDEX
    By jokorey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2014, 01:58 PM
  3. Replies: 1
    Last Post: 04-11-2013, 01:02 PM
  4. [SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete
    By rls231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:13 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