Excel2000 Issue 1

[lamaar]

I have two spreadsheets, one contains city ID's and country ID's (half of which are missing (over 6,000), amongst other things. My other spreadsheet just contains a list of all city ID's and their corresponding country ID's.

I need a formula which will look at the column in the first spreadsheet which contains a city ID, look at the column in the second spreadsheet which contains the corresponding city ID and then from this, get the country ID. The country ID then needs to be put in the first spreadsheet in the country ID column.

Make sense?

Lamaar75@hotmail.com

 

[xlbo]

Look in the help files for VLOOKUP
Will do exactly what you need. The formula would go in the CountryID column of the 1st spreadsheet

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lamaar]

xlbo, thanks fo the response.
I had tried to use VLOOKUP but can't get my head around it.

If my table was as set out below, what would the statement be?

A B C D
1 A A GB
2 A C GB
3 C NU B
4 NU CO GB
5 CO NN D
6 NN NE B

The formula would be placed in the b column. It would need to look at the character in the A column, look at column C and then find the appropriate country id in column D and place this in column B? Make sense?

Thanks

Lamaar75@hotmail.com

 

[xlbo]

yup
=vlookup($A1,$C$1:$D$7,2,FALSE)

essentially
$A1 - the cell you are trying to find a match for
$C$1:$D$7 - the range that includes the column to match on AND the data to return
2 - the number of columsn across to look
FALSE - return an exact match

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lamaar]

hey xlbo,

this worked great.

to be honest, i did have the formula.

the problem was the first rom which contained the lookup value. when i had exported it, it had exported the whole field length as opposed just the characters in that field. Therefore, the cell has the city id's plus a bunch of spaces.

Anyway, manged to sort this now with ur help.

thanks a lot

Lamaar75@hotmail.com

 

[xlbo]

aaah yes - that is a standard "issue" with vlookups - the TRIM function can be used in conjunction:
=vlookup(TRIM($A1),$C$1:$D$7,2,FALSE)
would work even where there are spaces at the end

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244