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

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

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

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

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