Index Match How to avoid blank cells

[lwilt]

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.

 

[lwilt]

Also posted at:

https://www.excelforum.com/excel-fo...tch-how-to-avoid-blank-cells.html#post4695592

https://www.mrexcel.com/forum/excel...-match-how-avoid-blank-cells.html#post4866015

 

[bosco_yip]

Upload a sample file with 10 rows of Input data together with the expected results.

Regards
Bosco

 

[NARAYANK991]

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

 

[lwilt]

I attached a sample workbook.

I will give that a try Narayan

 

[bosco_yip]

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

 

[lwilt]

that worked. thanks for all the help