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

[Lise C]

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

[#2 · Accepted solution]

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

[#3]

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.