If a Value Doesn't Exist in a Table, Return ""

I'm using the following formula:

=INDEX($B$2:$B$251,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A$2:$A$251,H2)),0),0))

If the value is present within column H and A, then it returns the corresponding value from column B. But if there's no match between columns B and H, I get a value of "0". How can I get a value of "" instead of "0" when there's no match?

Thanks!

That looks quite a complex way of doing an INDEX/MATCH.

It might help if you posted a sample workbook so that the formula can be seen in context.

Regards

There are three ways to do it, in order of efficiency:

1. Select the range in which that formula exists and then, right-click|Format Cells|Number Tab| Go to Custom and Type 0;-0;;@

2. Using conditional formatting.
# On the Format menu, click Conditional Formatting.
# In the box on the left, click Cell Value Is.
# In the second box from the left, click equal to.
# In the box on the right, type 0.
# Click Format, and then click the Font tab.
# In the Color box, select white.

3. Modifying your formula above:
=IF(INDEX($B$2:$B$251,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A$2:$A$251,H2)),0),0))=0,"",INDEX($B$2:$B$251,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A$2:$A$251,H2)),0),0)))

Wrap your function in an IF(ISERROR()) statement
e.g.
=IF(ISERROR("Your Formula"),"","Your Formula")

Or, if you only need to work with 2007 or later
=IFERROR("Your Formula,"")

Originally Posted by Ocean Zhang

Ron, I can't use your first 2 methods because i'm looking for something automated for future uses.

I don't see why this should be a problem. If you use a custom number format or conditional formatting on the formula it will be saved along with the workbook, and if you copy (or drag) your formula the formatting will go with it (unless you specify otherwise) as well)

Originally Posted by Ocean Zhang

Ron and Marcol,

I tried your formulas, but I couldn't get them to work

If your original formula works, then the error management or the zero-provision should work. If you notice I didn't change your formula, all I did was to enclose it within an IF formula.

Is this array formula what you mean?

In C1

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter
Then Drag/Fill Down