Forum Discussion

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

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

Here is my https://1drv.ms/x/s!Am-hfuhzP9qmgapc0okpIfjbREi2MA

(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

2 Replies

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