Match and replace with index

Hi,

We got three sheets in a excel file.
Sheet1: Got three columns. Street, City Name and State Name.
Sheet2: Got one column. City
Sheet3: Got one column. State

Now in Sheet1, I need to replace the City Name with the index number of that City in Sheet2. And the same way, in Sheet1, I need to replace the State Name with the index number of that State in Sheet3.

Please let me know, how can I do this in VBA?
Thank you very much in advance.

Hi Leith,

Thank you very much for reply.
I have attached two files:
Original file : Suburb_City_State_Example_Final.xlsx
Expected File: Suburb_City_State_Example_Final_Result.xlsx

No...I am not using this index number as a formula in the sheet.

In the attached fie, there are three sheets:
Suburb_City_State, City and State.

This is just the sample file. We got 200000 rows. We will prepare the CSV file which we can upload in the MySQL database.
In the sheet1 i.e. "Suburb_City_State", we got the list of Suburb with corresponding City and State in front of that Suburb.

Now first we want to replace City in sheet1 with the index number of that City in Sheet2 i.e. 'City'.
Then second we want to replace State in sheet1 with the index number of that State in Sheet3 i.e. 'State'.

Finally I will make one CSV file. Which will have only Sheet1 with Suburb Name with corresponding index number of city and state. This is to upload in MySQL database.

To answer your question regarding the index. In the sheet2 i.e. 'City', we have already placed the City in the sequence so that we can take its index(or number of row) to replace in sheet1.

Please let me know if you need more information. Thanks again.

Please...I think this is obvious. Somebody will have the ready made code. Thanks.

Thank you very much Leith. It works like a charm.

Mordred:
Sorry, I should have write "obvious for you guys".

Thanks.

Hi,

This program is working really nice. Please let me know, what should I do to achieve the following:
Currently we are replacing the City Name with number. Now I need to extend this little bit. In the sheet2 i.e. "City". In the first column there is the list of the city. Now I have added column B with some text in front of each city.
When I will run the function in sheet1. I want to replace the city name in sheet1, with the corresponding text in the column B in front of that city.
e.g. In sheet2 i.e. "City":
Sr. No. Column A Column B
1 Auckland City of Sails

Now in sheet1 stead of replacing "Auckland" with 1, it will replace it with text "City of Sails".
Please help me to do this. Thanks.

Thanks Leith. You are really helpful.