ISERROR in nested INDEX formula result NA#

[Vbapadawan]

I'm lost, I cannot find how to get the cell to display BLANK instead of NA# with the following code... Please help oh master of the Excel

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF($B$5:B23, List1), 0))), INDEX(List2, MATCH(0, COUNTIF($B$5:B23, List2), 0)), INDEX(List1, MATCH(0, COUNTIF($B$5:B23, List1), 0)))

 

[Takae]

Try this
=IF(ISERROR(INDEX(List1,MATCH(0,COUNTIF($B$5:B25,List1),0))),INDEX(List2,MATCH(0,COUNTIF($B$5:B25,List2),0)),INDEX(List1,MATCH(0,COUNTIF($B$5:B25,List1))))

Are List1 and List2 named range or just value?
If these are value, you have to put like "List1".

 

[Vbapadawan]

Sorry for the confusion Takae,

List1 (E8:E31) and List2 (F8:F31) are named ranges.
The formula looks at two columns of text on one worksheet and returns the unique value(s) to a range in another worksheet (B5:B25)

(

Try this
=IF(ISERROR(INDEX(List1,MATCH(0,COUNTIF($B$5:B25,List1),0))),INDEX(List2,MATCH(0,COUNTIF($B$5:B25,List2),0)),INDEX(List1,MATCH(0,COUNTIF($B$5:B25,List1))))

Are List1 and List2 named range or just value?
If these are value, you have to put like "List1".

 

[Vbapadawan]

nope that failed.....

Sorry for the confusion Takae,

List1 (E8:E31) and List2 (F8:F31) are named ranges.
The formula looks at two columns of text on one worksheet and returns the unique value(s) to a range in another worksheet (B5:B25)

(

 

[Vbapadawan]

found it for those looking...Since you're actually checking two lists, you'll want to do 2 error checks

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF($B$5:B5, List1), 0))),
 IF(ISERROR(INDEX(List2, MATCH(0, COUNTIF($B$5:B5, List2), 0))),"",INDEX(List2, MATCH(0, COUNTIF($B$5:B5, List2), 0))),
INDEX(List1, MATCH(0, COUNTIF($B$5:B5, List1), 0)))

 

[Aladin Akyurek]

Add the following code to your workbook, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Define Data in Name Manager as referring to:

=arrayunion(List1,List2)

Note. You already have List1 and List2.

Define Ivec in Name Manager as referring to:

=ROW(INDIRECT("1:"&COLUMNS(Data)))

In B3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Data<>"",
  MATCH("~"&Data,Data&"",0)),Ivec),1))

In B4 enter: Unique list

In B5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$5:B5)<=$B$3,
  INDEX(Data,SMALL(IF(FREQUENCY(IF(Data<>"",
  MATCH("~"&Data,Data&"",0)),Ivec),Ivec),ROWS($B$5:B5))),"")