Index Match How to avoid blank cells

lwilt

Board Regular
Joined
May 16, 2013
Messages
187
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.
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
@lwilt, see if this does what you need:

=INDEX($F$2:$F$2000,(SUMPRODUCT(--($A$2:$A$2000=A1)*($F$2:F$2000<>"")*ROW($F$2:$F$2000))-1))
 

lwilt

Board Regular
Joined
May 16, 2013
Messages
187
I'll give it a try. I've also posted a sample workbook on the other two sites with the links posted since I can't figure out how to attach a doc in this thread.
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
Deleted
 
Last edited:

lwilt

Board Regular
Joined
May 16, 2013
Messages
187
awesome it's been solved. thanks for the help
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
@lwilt, could you let us (and future readers) know which solution/formula solved the issue, particularly given that this was cross-posted?
 

lwilt

Board Regular
Joined
May 16, 2013
Messages
187
Sure can. I ended up using:

=IFERROR(INDEX(Sheet2!$F$2:$F$15,SMALL(IF((Sheet2!$A$2:$A$15=Sheet1!$C2)*(Sheet2!$F$2:$F$15<>""),ROW($F$2:$F$15)-ROW($F$2)+1),1)),"")

and activating the array with the curly brackets around it.

I saw a few other posts had shorter formulas so I'm going to try those out later today but I do know the one above worked.
 

Forum statistics

Threads
1,078,211
Messages
5,338,888
Members
399,265
Latest member
aj17x55

Some videos you may like

This Week's Hot Topics

Top