Index Match How to avoid blank cells

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.

Also posted at:

https://www.mrexcel.com/forum/excel-...ml#post4866015
http://chandoo.org/forum/threads/ind...k-cells.35036/

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I've attached a sample workbook.

In I2

=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)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

awesome that worked. thank you.