match

[borono9]

This is complicated as I probably have not expained myself propery bu
here goes:
I have a fixture list (home team v away team) and two sets of leagu
tables (home statistics and away statistics) I aquire this data fro
the web using web query I would like to match the data from the leagu
table to the fixture list of the corresponding teams (which I can d
manually) but I would like to do this automatically.The problem I hav
is that if I try to match the team in the fixture list with it
corresponding data in the home or away table I am unable to do this a
the team name don't quite match as the team name in the data table ha
a number inside ()after the team name .is there anyway I can match th
fixtures with the data using only part of the names

 

[Ken Wright]

How many numbers after the name. Is it directly after the name or separated by
a space, and does it go past 9, ie a double digit figure.

 

[borono9]

there is a space after the team and the number ranges from 1 - 24 eg
team (1) to team (24)

 

[Ken Wright]

OK, do you have any other spaces in the names besides that one you have just
mentioned?

 

[Ken Wright]

Actually forget that. Assuming the list of names you are looking up is in
A1:A20, and the list you are looking to match it against is in say K1:K100, then
in B1 put the following formula and array enter it using CTRL+SHIFT+ENTER

=IF(ISNA(MATCH(A1,MID($K$1:$K$100,1,LEN(A1)),0)),"Not
found",MATCH(A1,MID($K$1:$K$100,1,LEN(A1)),0))

Now just copy it down to B20

If that string exists in the master list in K1:K100 then you will see a number
showing you what position it is in in the list, and if it doesn't exist then it
will display 'Not Found'

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

OK, do you have any other spaces in the names besides that one you have just
mentioned?

 

[borono9]

ken this works to a certain degree but unfortunately some names hav
been shortened eg sheffield is shortened to sheff should this formula
still work?
cheers
:confused

 

[Ken Wright]

Depends where they have been shortened. If the name you are looking up (ie the
ones without the numbers) is what has been shortened, and the list you are
looking it up in has not, then no problem, but if it is the other way round then
it will not work.