Replace cells in column B based on column A

[hotpinkleg]

I feel like this is a basic question but can't figure out how to automate it with a macro.

Example of what I'm trying to do:
Column A has Cities and Column B has the time zone. I need to replace the time zone based on the city to the state it belongs in.

I know how to set it up the slow way of creating a new tab and copying the cities and then in the next column the matching state and then creating the formula but need to make this easy for the rest of my team (ie: click this macro button) if I'm out of office.

City	Time Zone
Detroit	East
New York	East
Chicago	Central
Dallas	Central
Portland	West
Denver	Mountain
Colorado Springs	Mountain
Lansing	East
San Francisco	West

 

[Zot]

You still need a table somewhere with matching time zone for a list of cities, but then VLookUp will automatically change column B.

 

[hotpinkleg]

Sorry, I can read VBA code much better than I can write it myself. wouldn't a vlookup just find if the value exists on another table?
I thought I would be using an IFS statement?

 

[hotpinkleg]

Actually, I think an Xlookup formula would be easier

=XLOOKUP((A:A),reference city list,reference cooresponding state list,"")

How do I make this into a VBA macro?

 

[Zot]

Actually, I think an Xlookup formula would be easier

=XLOOKUP((A:A),reference city list,reference cooresponding state list,"")

How do I make this into a VBA macro?

Even XLookUp need to refer to table too, right?

I'm not sure if I understood your question right. It seems like you do not want a table but even VBA would need to refer to a table somewhere. You would not want to put all the list in code because it would be impractical.

 

[hotpinkleg]

It's ok, I figured out how to edit the power bi report so I don't need to edit anything in excel anymore. Thanks!