How do I create an IF function to include an array?

[hnhorner]

For example, column C has a list of city names from around the country.

Column G includes a list of all the cities that we service.

I want to create a formula that says, "if the city name in column C is anywhere in the array G2:G200, display the city name in column D (in the respective row), otherwise, display "none."

Please help and thank you!

[mcmahobt]

You don't necessarily have to use an IF statement for this. You could use an INDEX/MATCH combo. Try this:

Please Login or Register  to view this content.

Tailor the G column references to match your data set, and make sure the C column reference is on the first cell that contains data.

Will something like that work?

[daffodil11]

=IFERROR(INDEX(G2:G200,MATCH(C2,G2:G200,0)),"NONE")

or

=IFERROR(LOOKUP(2,1/(G2:G200=C2),G2:G200),"NONE")

or

=IF(ISNUMBER(SEARCH(C2,G2:G200)),G2:G200,"NONE")

or

=IF(COUNTIF(G2:G200,C2),C2,"NONE")

[hnhorner]

You are brilliant and just saved me days of work. Thank you very much and happy holidays!!

[daffodil11]

Happy holidays