Forum Discussion

Lise C's avatar
Lise C
Copper Contributor
Mar 12, 2018

Eliminate #N/A in Index Match that looks at a List

Here is my shared (and editable) worksheet

(shoot - the formulae disappear in the link). Here they are:

Col1: 

=INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0))

 Col2:

=IF(OR(A2={"congo","Democratic Republic of Congo"}),"Congo",
IF(OR(A2={"czech","Czechoslovakia"}),"Czechia",
IF(OR(A2={"america","u.s.a.","united states","United States"}),"USA",
IF(OR(A2={"united kingdom","England","britain","U.K.","Great Britain","British"}),"UK",
IF(OR(A2={"russia","ussr","u.s.s.r","Russian Federation"}),"Russia",
A2)))))

Col 3 (just in case)

=IF(ISNUMBER(SEARCH(B2,OrigData!B2,1)),B2,OrigData!B2)

I'm trying to remove all #N/A in the sheet and replace with just blank/empty cells.

I can usually figure stuff out, but I'm too unfamiliar with Index and Match especially when they compare items in a column to items on a named list.  

 

ColA finds words that match words in my named CountryList, and Col B is used to transform some country names.

 

Let me know if you need additional information.
Thanks!

Lise

  • Hi Lisa,

     

    Easily, you can wrap the formula with one of these functions:

    • IFNA
    • IFERROR

     

    Examples:

    =IFNA(INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0)),"")

    =IFERROR(INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0)),"")

     

    NOTE: IFNA is available in Excel 2013 or later.

     

    Hope that helps

    Haytham

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Lisa,

     

    Easily, you can wrap the formula with one of these functions:

    • IFNA
    • IFERROR

     

    Examples:

    =IFNA(INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0)),"")

    =IFERROR(INDEX(CountryList,MATCH(TRUE,ISNUMBER(SEARCH(CountryList,OrigData!O18)),0)),"")

     

    NOTE: IFNA is available in Excel 2013 or later.

     

    Hope that helps

    Haytham

    • Lise C's avatar
      Lise C
      Copper Contributor
      Haytham. That did the trick.
      Thank you, not just for giving me an answer, but for helping out others who need help. Your time and effort are appreciated.

Resources