How to get rid of #NUM!

[insp562]

Hi! I don't know much about formulas but found this one here. Is there any way to get rid of #NUM! when this formula doesn't find anymore results?

=INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",ROW($B$3:$B$10)
-ROW($B$3)+1),ROWS($D$3:D3)))
(Formula edited for this example)
A B C D 1 time name LA2
3 LA John John
4 NY Roland Charles
5 NY Mario #NUM! I need these spaces.
6 LA Charles #NUM! I need these spaces.
etc..

Thank you in advance for the waited reply!

 

[mancemonster]

Try...

=IF(ISERROR(Your Formula),"",(Your Formula))

Regards

Roger

 

[insp562]

I tried but keep getting an error message.

 

[Michael M]

This is exactly what Roger has suggested, and it works fine for me !

=IF(ISERROR(INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3)))),"",INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))))

 

[T. Valko]

Hi! I don't know much about formulas but found this one here. Is there any way to get rid of #NUM! when this formula doesn't find anymore results?

=INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",ROW($B$3:$B$10)
-ROW($B$3)+1),ROWS($D$3:D3)))
(Formula edited for this example)
A B C D 1 time name LA2
3 LA John John
4 NY Roland Charles
5 NY Mario #NUM! I need these spaces.
6 LA Charles #NUM! I need these spaces.
etc..

Thank you in advance for the waited reply!

Try it like this...

D1 = LA

Enter this formula in D2. This will return the the count of records for LA or whatever you enter in D1.

=COUNTIF(A3:A10,D1)

Enter this array formula** in D3. This will extract the names from column B that correspond to LA in column A.

=IF(ROWS(D$3:D3)>D$2,"",INDEX(B:B,SMALL(IF(A$3:A$10=D$1,ROW(A$3:A$10)),ROWS(D$3:D3))))

** 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.

Copy down until you get blanks.

 

[touque]

Thanks Roger it worked great for me.

 

[Aladin Akyurek]

Thanks Roger it worked great for me.

ISERROR leads to computing the same thing twice, something we can better avoid...

If you have IFERROR (Excel 2007 or later), try...

D3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",
    ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

Otherwise, as already suggested to you...

D1: LA

D2, jur enter:

=COUNTIF(A3:A10,D1)

D3, control+shift+enter, not just enter:

=IF(ROWS($D$3:D3)<=$D$2,
   INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10=$D$1,
    ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

Avoiding D1 and D2 would again rise the costs...

D3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)<=COUNTIF($A$3:$A$10,"LA"),
    INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",
     ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

 

[T. Valko]

ISERROR leads to computing the same thing twice, something we can better avoid...

If you have IFERROR (Excel 2007 or later), try...

D3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",
    ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

Otherwise, as already suggested to you...

D1: LA

D2, jur enter:

=COUNTIF(A3:A10,D1)

D3, control+shift+enter, not just enter:

=IF(ROWS($D$3:D3)<=$D$2,
   INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10=$D$1,
    ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

Avoiding D1 and D2 would again rise the costs...

D3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)<=COUNTIF($A$3:$A$10,"LA"),
    INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",
     ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

=IF(ROWS(D$3:D3)>D$2,"",INDEX(B:B,SMALL(IF(A$3:A$10=D$1,ROW(A$3:A$10)),ROWS(D$3:D3))))

Is more efficient than:

=IFERROR(INDEX($B$3:$B$10,SMALL(IF($A$3:$A$10="LA",
ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($D$3:D3))),"")

Calculating a row offset is not needed and, as you put it, costly.

 

[insp562]

I tried it again and it worked perfectly. Thank you very much Roger. Thanks to Michael M, Bif and Aladen too.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>