#N/A Error in Array Formula

**jrarmstrong4** · 11-30-2011, 04:06 PM

Hi,

I am using the following array formula to copy certain text from another cell:

=INDEX(E1:E100,MATCH(1,IF(A1:A 100=Selections!J4,IF(ISNUMBER(MATCH(E1:E100,{"F ","U"},0)) ,1)),0))

I am getting the #N/ A error in the cell when the source cell contains something other than "F" or"U". I have tried to wrap the ISERR command around the formula but this does not seem to work. Is there another way of getting rid of the #N/A and just leaving a blank cell?

Thanks,

James

**NBVC** · 11-30-2011, 04:08 PM

Did you try IFERROR? As in:

=IFERROR(INDEX(E1:E100,MATCH(1,IF(A1:A 100=Selections!J4,IF(ISNUMBER(MATCH(E1:E100,{"F ","U"},0)) ,1)),0)),"")

**jrarmstrong4** · 11-30-2011, 04:24 PM

Thanks for the quick response. I hadn't tried that - still learning Excel - but have done so now. It gives the #NAME? error message this time. Any other ideas?

Thanks,

James

**NBVC** · 11-30-2011, 04:29 PM

You are using XL2007?

if not, then what kind of values are your supposed to return from column E, numbers or text?

**jrarmstrong4** · 11-30-2011, 05:02 PM

No we're still on 2003 here.

The content of the source cell will be either a letter (including F and U) or blank.

Thanks,

James

**NBVC** · 11-30-2011, 05:18 PM

tRY:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER

**jrarmstrong4** · 11-30-2011, 05:50 PM

That works perfectly. Thanks again for your help.

James

**NBVC** · 11-30-2011, 06:02 PM

Great!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save