+ Reply to Thread
Results 1 to 7 of 7

INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

  1. #1
    Registered User
    Join Date
    04-06-2019
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    11

    INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    Hi Everyone,

    Im not sure if my title is understandable, but here is my problem.


    Im using INDEX MATCH function to find the Item Code with Add-in Code as the pivot.
    There will be one Item Code for a few Add-in Code that sit vertically.

    Using INDEX MATCH function, I can retrieve any cell that has the Add-in Code matches.
    Only those that has Item Code beside the Add-in Code will yield the result I want.
    Those without Item Code beside it will yield "0", meaning it matches but there is no value in the resulted cell.
    Therefore, Im looking for a formula that able to search and find the cell above the resulted cell (since the resulted cell is blank), in addition to the INDEX MATCH formula.


    Thanks


    Iwan

    Than
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,469

    Re: INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    In A4 copied down (helper column):

    =IF(B4="",A3,B4)

    Then:

    =INDEX(A:A, MATCH(E4,C:C,0))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    04-06-2019
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    11

    Re: INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    Hi Ali,

    Yes, this will definetely works to my problem. Just by adding a helper column that I can throw at the back of the sheet can shortcut the formula.
    Why can't I think of this hahaha

    Problem Solved.


    Thanks Ali for taking your time answering my question.

    *Note: If you have the intended formula, I'm still willing to learn as well


    Thanks

    Iwan

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,469

    Re: INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    I don't understand what you mean by "intended formula".

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    04-06-2019
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    11

    Re: INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    Yes, I will mark this thread as solved. As for the formula, I was thinking whether is it possible to do it without the helper column.

    But its not a big deal since you solved my problem .


    Thanks Ali

  6. #6
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,095

    Re: INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    You could try this in F4:
    =IF(B4="",F3,INDEX(B:B, MATCH(E4,C:C,0)))
    and copy it down.

  7. #7
    Registered User
    Join Date
    04-06-2019
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    11

    Re: INDEX MATCH problem that needs to seach row ABOVE if result cell is blank.

    Hi Greg,

    Unfortunately it cannot be done that way since Add-in Code in Column C is often messy and not from the same group category. In this case is category 'S'. If the Add-in Code is from different category (let's say 'R'), then, the INDEX MATCH formula will also be conducted from sheet R.

    May be I should replace Item Code with Main Part and Add-in Code with Spare Part. May be it is easier to understand that way.

    Thanks Greg


    Iwan

+ 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] Is there anyway to Match() an actual blank cell with a blank formula result?
    By vwhite in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-15-2020, 09:38 AM
  2. [SOLVED] INDEX/MATCH generates incorrect result by referencng nearest cell if match not found
    By aglawrence in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2017, 08:59 AM
  3. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  4. Excel index match, don't want blank cells in result
    By sambak2 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-20-2014, 05:22 AM
  5. [SOLVED] Need help with Index/Match & 0/blank vs actual 0 problem
    By fhill in forum Excel General
    Replies: 5
    Last Post: 02-21-2014, 05:17 PM
  6. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 AM
  7. seach and match to give result
    By prasjohn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2009, 06:19 AM

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