Changing text in column A based on text in Column B

[JordynMossFPU]

Hello Experts!

I am trying to find a formula that will allow me to change text in one column based on text in another column. More specifically, we have had turnover of Admissions Counselors, and I need to adjust old data to reflect current data. We assign counselors based on territory. Therefore, I need to change all student records who are assigned to counselor A, to Counselor B, based on the city that they live in. Column A has counselor initials. Column B has the city that the student is from. I need excel to change the counselor initials to match certain cities based on current territories, and I've been trying to use VLOOKUP or an IF function, but I'm having no luck. We have thousands of records in our data set, so I'm looking for something faster than myself scrolling through and manually changing the data myself. Any help would be incredibly appreciated! Thank you all!

[#2 · Accepted solution]

@JordynMossFPU 

=IF(NOT(ISNA(MATCH(B2&C2,$G$4:$G$7&$F$4:$F$7,0))),INDEX($H$4:$H$7,MATCH(B2&C2,$G$4:$G$7&$F$4:$F$7,0)),A2)

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

In the example there is a table with new counselors initials for certain territories and cities in range F4:H7. If there is a new counselor the formula returns the initials in column D based on territory and city. If there isn't a new counselor the formula returns the initials of the counselor from column A. 

[#3]

Thank you so much! I am going to try implementing this formula.
Thanks again!