Find #NA Error Using Lookup Function

[gnaga]

Hi, All

Is this possible to find the first appearance of #NA error in a given range using any of the Excel Lookup Function? I would like to pick the data from one cell above where this #NA appears in my range.

 

[Aladin Akyurek]

Hi, All

Is this possible to find the first appearance of #NA error in a given range using any of the Excel Lookup Function? I would like to pick the data from one cell above where this #NA appears in my range.

Control+shift+enter, not just enter:

=INDEX(A2:A14,MIN(IF(ISNA(A2:A14),ROW(A2:A14)-ROW(A2)+1))-1)

 

[T. Valko]

Hi, All

Is this possible to find the first appearance of #NA error in a given range using any of the Excel Lookup Function? I would like to pick the data from one cell above where this #NA appears in my range.

Try this...

Book1
	A	B	C
2	68	_	86
3	58	_	_
4	22	_	_
5	86	_	_
6	#N/A	_	_
7	81	_	_
8	4	_	_
9	93	_	_
10	43	_	_
Sheet1

This array formula** entered in C2:

=INDEX(A2:A10,MATCH(TRUE,ISNA(A2:A10),0)-1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes the #N/A will never be in the first cell of the range as there would be no cell above it!

 

[gnaga]

I tried both of your Array Formula. Both returns #NA error.

My range is O4:O1443 in that #NA error will appear in between and all the bottom cells since the first appearance of #NA error.

 

[T. Valko]

I tried both of your Array Formula. Both returns #NA error.

My range is O4:O1443 in that #NA error will appear in between and all the bottom cells since the first appearance of #NA error.

Did you enter the formula as an array formula?

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

Here's a small sample file that demonstrates this.

ZZZgnaga.xls 15kb

http://cjoint.com/?AFAfe7Iitm6

As you can see, the formula does return the correct result.

 

[Aladin Akyurek]

I tried both of your Array Formula. Both returns #NA error.

My range is O4:O1443 in that #NA error will appear in between and all the bottom cells since the first appearance of #NA error.

Adjusting the range and applying control+shift+enter should yield a non #N/A answer. By the way, the MATCH version is faster. If there is no #N/A in the range of interest, the formula will yield a diagnostic error.

 

[gnaga]

Thanks.

It is working fine after I enter the arraay formula correctly. Earlier I was doing some mistake in entering the formula.

 

[dabutler]

I realize this is an old thread. I had came up with the idea to use the =sum() formula because it always throws an error if an error is included in the range is it summing.

My formula looks like =IF(IFERROR(SUM(RANGE),"Check")<>"Check","Ok","Check"). This will either tell me its Ok or to Check the Range.

 

[Aladin Akyurek]

I realize this is an old thread. I had came up with the idea to use the =sum() formula because it always throws an error if an error is included in the range is it summing.

My formula looks like =IF(IFERROR(SUM(RANGE),"Check")<>"Check","Ok","Check"). This will either tell me its Ok or to Check the Range.

Not sure whether you are offering a solution for the original poster (OP) or you have a question yourself.

If the former, mind you the OP was asking to caluculate MIN of a range while that range is infested with #N/A's.

If the latter, try to elaborate unless you are trying to SUM a range which contains #N/A or other values:

=SUM(SUMIFS(RANGE,RANGE,{"0<",">0"}))