Need help with a formula

[haginho]

Hi everyone,



I am new here so please allow me to greet you, the kind future helper and current reader. I am trying to create a formula that would check values in column with country codes, compare it to a list of valid country codes and if it finds one, check another column with full country names and compare it to a list of valid country names. In essence what my task comes down to is that I have a very long list of records with user account details and need to validate if all of them have country code assigned and if it matches the country name to remove users with missing or incorrect data. My approach to this task would be to create a table of all country codes and country names and perhaps use vlookup or if function to browse through the user list. I will higly appreciate your help with this task.

Thank you very, very much (in advance, but will surely thank afterwards as well),

Jan

[gebobs]

Please attach a sheet with a few lines of sample data.

[haginho]

Please find attached (not the original data, but it fully emulates the task ahead)

The formula should display true if the user has a country code and country name assigned and it matches the data in the second table so in the case of Patrick Sandwich it should show false (I didn't add an example where data in one field is missing but it should also show as false).

[gebobs]

=if(and(countif(i:i,c2)>0,d2=vlookup(c2,i:j,2,false)),true,false)

[haginho]

I haven't tested it yet and will do tomorrow when I'm at the office, but I already want to express my gratitude for your kindness, thank you so much!