SOLVED

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

New Contributor

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 Replies
best response confirmed by Lise C (New Contributor)
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

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.