An INDEX MATCH formula has blank cell - need to ignore or delete

Hello,

I've spent several hours searching for a solution and couldn't quite find the answer.

My workbook has two worksheets, the Invoice Template and the Customer Data.

On the Invoice Template, there is a drop-down used to select the customer. When a customer is selected, I have an INDEX MATCH formula to populate the address information into the cells below. This works fine, except if the customer doesn't have any data in the "Attn" column of the Customer Data worksheet. I can make it blank, but what I would really like is for the blank cell to either be ignored and filled with the next cell, or deleted and cells shift up (automatically when the customer is selected).

Please let me know if you need any additional information.

Thanks!
Invoice Test.xlsm

Hi,

you can use the iferror function.
ex

Formula:

Please Login or Register  to view this content.

I probably should have put the formula I am using in my post. I already have an "IFERROR" formula that returned "0." I used the modified formula from Mayda89, which returns a blank cell. But, I don't want 0, blank, etc. I want the blank cell gone and the next row of data to take it's place. In other words, I want the address to read:

Customer [from column1 of data]
Address [column3]
City, State ZIP [column4]

NOT:

Customer [column1]
Blank Space [column2]
Address [column3]
City, State ZIP [column4]


The reason there is a blank space is that sometimes the customer will have two address lines, like an "Attn:" or Suite number, Box number, etc. Sometimes not (resulting in blank). For example, if you choose "MACY'S" from the dropdown, the space is not blank.


Hope that helps explain and if anyone knows how to solve this I would love to hear from you. Thanks!

Thank you, that solves the problem. I will close this thread.

Really appreciate the response and time.