Forum Discussion
Help with Data formula
I have the setup the following formual to find Hispanic Surnames in column A:
=IF(RIGHT(A2,2)="ez", "Hispanic", IF(ISNUMBER(MATCH(RIGHT(A2,LEN(A2)-FIND(" ",A2)),HispanicSurnames,0)), "Hispanic", "Non-Hispanic"))
It is accurately finding any last name ending in ez but it is not also matching any names i have in the "hispanicsurnames" list
Here is a sample data:
| LNAME | FNAME | MI | Surnames | HispanicSurnames |
| TAVERAS | YVONNE | J | Non-Hispanic | BONILLA |
| BONILLA | MABEL | L | Non-Hispanic | TAVERAS |
| LARK SNELL | SHEILA | N | Non-Hispanic | GARCIA |
| FLORES | SANDRA | Non-Hispanic | FORES | |
| MAYS | INGER | Non-Hispanic | SOSA | |
| RICKS | SHIRLEY | Non-Hispanic | AYESTAS | |
| HARDLEY | SHAUNA | L | Non-Hispanic | RODRIGUEZ |
| JUND | JENNISE | M | Non-Hispanic | SANTOS |
| BRIMGRANT | KEENAN | Non-Hispanic | ||
| KNIGHT | YVONNE | M | Non-Hispanic | |
| FIGUEROA | MARIA | Non-Hispanic | ||
| MASON | REGINALD | Non-Hispanic | ||
| WILLIAMS | STEPHEN | R | Non-Hispanic | |
| CONYERS | BURNESTEEN | P | Non-Hispanic | |
| ALSTON | WESLY | T | Non-Hispanic | |
| GREENE | JAMES | D | Non-Hispanic | |
| CLARKE | GARFIELD | A | Non-Hispanic | |
| SANCHEZ | EDDY | A | Hispanic |
1 Reply
How about
=IF(OR(UPPER(RIGHT(A2,2))="EZ",ISNUMBER(MATCH(A2,HispanicSurnames,0))),"Hispanic","Non-Hispanic")
