Excel Formula Help
 

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns.
What I did was, I created a table of Cities of South Africa and in which
province they are in.
I need to match the name of the city found in the entry text to the nam of
the city in the table that I have created and the result should be the
province.
Can this be done ?

Excel Formula Help
 

use the following function

=index(array,,column_num)




"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns.
What I did was, I created a table of Cities of South Africa and in which
province they are in.
I need to match the name of the city found in the entry text to the nam of
the city in the table that I have created and the result should be the
province.
Can this be done ?

Excel Formula Help
 

A2=City (from your text)
Sheet2 columns A&B contain table of Cities vs Province

=VLOOKUP(A2,SHEET2!a:b,2,0)

will return province

Or

=if(isna(VLOOKUP(A2,SHEET2!a:b,2,0)),"",VLOOKUP(A2 ,SHEET2!a:b,2,0))

will return blank if not found

HTH


"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns.
What I did was, I created a table of Cities of South Africa and in which
province they are in.
I need to match the name of the city found in the entry text to the nam of
the city in the table that I have created and the result should be the
province.
Can this be done ?

Excel Formula Help
 

Thanks Toppers, but A2 doesn't only contain the city.
This is the sample of the two samples where C2 & C3 in the entry data
contains the city.

Entry Data

A B C D
1 Date Cell Number Message Province
2 2007/04/27 2782xxxxxxx 324De Port Elizabeth
3 2007/04/28 2772xxxxxxx 856Et Cape Town


City List

A B
1 Name Province
2 Port Elizebeth Eastern Cape
3 Cape Town Western Cape


"Toppers" wrote:

A2=City (from your text)
Sheet2 columns A&B contain table of Cities vs Province

=VLOOKUP(A2,SHEET2!a:b,2,0)

will return province

Or

=if(isna(VLOOKUP(A2,SHEET2!a:b,2,0)),"",VLOOKUP(A2 ,SHEET2!a:b,2,0))

will return blank if not found

HTH


"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns.
What I did was, I created a table of Cities of South Africa and in which
province they are in.
I need to match the name of the city found in the entry text to the nam of
the city in the table that I have created and the result should be the
province.
Can this be done ?

Excel Formula Help
 

Thanks for your reply Cresta, I tried yo formula but it didn't work.
You the problem comes in where it is not only the City that will be in a
cell but a string eg. Hi I live in Pretoria. I need to know how I can search
that cell for the city that matches in the table on the City sheet rang
A2:A100 and the results is the corresponding value in sheet [City] B2:B100.

Hope that makes some sense.

"Cresta" wrote:

use the following function

=index(array,,column_num)




"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns.
What I did was, I created a table of Cities of South Africa and in which
province they are in.
I need to match the name of the city found in the entry text to the nam of
the city in the table that I have created and the result should be the
province.
Can this be done ?

Excel Formula Help
 

If the format of column C is always as your example, then:

=MID(C1,7,255) will get the City

so try ......

=VLOOKUP(MID(C1,7,255),SHEET2!a:b,2,0)

HTH


"Roseygains" wrote:

Thanks Toppers, but A2 doesn't only contain the city.
This is the sample of the two samples where C2 & C3 in the entry data
contains the city.

Entry Data

A B C D
1 Date Cell Number Message Province
2 2007/04/27 2782xxxxxxx 324De Port Elizabeth
3 2007/04/28 2772xxxxxxx 856Et Cape Town


City List

A B
1 Name Province
2 Port Elizebeth Eastern Cape
3 Cape Town Western Cape


"Toppers" wrote:

A2=City (from your text)
Sheet2 columns A&B contain table of Cities vs Province

=VLOOKUP(A2,SHEET2!a:b,2,0)

will return province

Or

=if(isna(VLOOKUP(A2,SHEET2!a:b,2,0)),"",VLOOKUP(A2 ,SHEET2!a:b,2,0))

will return blank if not found

HTH


"Roseygains" wrote:

Hi there,
I work in the mobile industry running campaigns and so forth. We received
the sms entry report in a excel spreadsheet.
What I need to do is find the region of entrants. The Entry Data report has
the date, Cell number and text which was entered across columns.
What I did was, I created a table of Cities of South Africa and in which
province they are in.
I need to match the name of the city found in the entry text to the nam of
the city in the table that I have created and the result should be the
province.
Can this be done ?