-----Original Message-----
Use the VLOOKUP formula. I just posted the text below to another question, so
it may help you also:-
Assuming the data you want is in File a.xls in the range A1:F100. Further
assume that the value you are looking to match on is in cell A1 in b.xls.
Now, in say cell B1 in b.xls put the following formula, but obviously change the
range to suit
=VLOOKUP($A$1,'C:\Documents and Settings\Default\My
Documents\[a.xls]Sheet1'!$A$1:$F$100,2,0)
and you will see that it now brings back the relevant value.
In C1 put the following formula
=VLOOKUP($A$1,'C:\Documents and Settings\Default\My
Documents\[SourceData.xls]Sheet1'!$A$1:$F$100,3,0)
and it will bring back the next value for that record - Note that the only thing
that changed was the 2 to a 3. The explanation below this will tell you why.
Just before that though, you should realise that if the source file a.xls IS
OPEN, then you won't see the full file reference in the formula, and all you
will see is this:-
=VLOOKUP($A$1,[a.xls]Sheet1!$A$1:$F$100,2,0)
If your sheet name has any spaces in it, then you also need single quotes around
the reference, eg assuming your sheet was named Sheet 1 instead of Sheet1, it
will look like this:-
=VLOOKUP($A$1,'[a.xls]Sheet 1'!$A$1:$F$100,2,0)
If you now close file a.xls and go back and look at the formula in b.xls you
will see that they now all have the full file path in the reference.
VLOOKUP EXPLANATION:-
The way VLOOKUP works is that if, as you have done, you define a table with a
name eg MyTable and that table refers to the range say A1:B100, then the formula
=VLOOKUP("a",MyTable,2,FALSE) FALSE can also be written as 0, eg:-
=VLOOKUP("a",MyTable,2,0)
will go to the range MyTable (A1:B100), and look in the leftmost column of that
Table to try and find the value "a", (if you have a 0 or FALSE at the end it
will look for an exact match only!!), and if it finds it, it will return the
value from the 2nd column (Because of the 2 in the formula).
If it doesn't find an exact match then it will return #N/A which means it
doesn't exist. It may appear to that the value is there, but a leading or
trailing space may make the data look identical, but will in fact mena that it
is different, eg:-
John
John
These two are NOT the same. The second John has 2 spaces after it, but visually
you could never tell that.
If you had had 5 columns in your table, so that the range MyTable referred to
was say A1:E100, then the 2 in the formula could be changed to 3 or 4 or 5, and
the formula, if it indeeds finds a match will then return the corresponding
value from Col C, D or E depending on that number.
If you omit the last optional argument of FALSE or 0, then Excel will look for
the nearest match to the value you have asked it to look up (See Help for more
detail), and this is often used when looking a table of values for say a
discount or commission structure, eg:-
A B
10 10%
50 20%
100 30%
150 40%
200 50%
250 60%
=VLOOKUP(65,MyTable,2) assuming MyTable was A1:B100 would return 20% as it
would 'find' 50 as being the largest number smaller than your target number, and
then give you the corresponding value from the 2nd Column of MyTable.
If you don't use the last argument of FALSE or 0 then the data in the first
column of your table MUST be sorted, but with the last arg of 0 or FALSE it does
not need to be.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------
hi every one i need help with the code in excel
i have a data base (a.xls)in which i have the
the name age sex Address City Zip
ex
Name Age Sex Address City Zip
a 13 f xyz Wa 12345
b 22 m assa ca 23456
c 34 f etert io 31333
what i want to is in another excel sheet(ex b.xls)
when i enter only the name it should add the rest
ie when i enter the name it should add the age sex address
etc
can any one help me
tx
suresh
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
.
