Forum Discussion
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 AmairahSilver 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 CCopper ContributorHaytham. 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.