• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match How to avoid blank cells

lwilt

Member
Hi,

I'm trying to use an index match and the column where I'm trying to return data has blank cells in it. The formula I tried but didn't work was:

=INDEX(F2:F2000,IF(F2:F2000<>"",MATCH,A1,A2:A2000,0)))

The problem I'm running into is that the data I'm trying to return on an entry occurs after a blank and that entire entry is skipped. Below is an example so you can see what's causing my issue.

order A return data
order A blank
order B blank
order B return data
order B return data

So order A will allow to me return the information I'm trying to grab for that order but on B because it's first cell is blank all of order B is skipped and I can't return that piece of data I'm trying to get.

thanks for the help.
 
Hi ,

Try this :

=INDEX($F$2:$F$2000, SMALL(IF($A$2:$A$2000 = $A$1, IF($F$2:$F$2000 <> "", ROW($A$2:$A$2000) - MIN(ROW($A$2:$A$2000)) + 1)), ROWS($A$1:$A1)))

This is an array formula , to be entered using the key combination of CTRL SHIFT ENTER.

Narayan
 
I attached a sample workbook.

I will give that a try Narayan
 

Attachments

  • index match ex file.xlsx
    147.2 KB · Views: 4
Maybe,

In I2, formula copy down :

=IF(COUNTIFS(Sheet2!A:A,C2,Sheet2!F:F,"Custom")>0,"Custom","")

or,

=IF(COUNTIFS(Sheet2!A:A,C2,Sheet2!F:F,"><")>0,"Custom","")

Regards
Bosco
 

Attachments

  • index match ex file.xlsx
    14 KB · Views: 10
Last edited:
Back
Top