Matching databases

[Jonas]

Hi,
is there a way to match data from two excel workbooks
based on a variable common in both workbooks? This is
very common task in database software like filemaker but
I have not figured out how to do it in excel yet.

ex. I have two databases in excels containing two
different kind of data (i.e adress and phonenumber)for
the same subject (i.e name). How do I create a
crossreference between the two databases that finds the
right adressa and phonenumber for each unique name?

Regards
Jonas

 

[Bernie Deitrick]

Jonas,

Use a VLOOKUP formula - see help for more on how - and set the fourth
optional parameter to FALSE.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Jonas,

Actually, using VLOOKUP() is the way to do that.

For example, if your key number is in column A and your data is in
columns B and C of each workbook, then in cell D2 on Sheet1 of the
first workbook, use the formula (assumes labels in row 1):

=VLOOKUP(A2,[Book2.xls]Sheet1!$A:$C,2,FALSE)

will bring in the second column's value for the key number in cell A2.
And this in cell E2

=VLOOKUP(A2,[Book2.xls]Sheet1!$A:$C,3,FALSE)

will bring in the third column's data.

Copy those formulas down to match all your key values, then copy them
and pastespecial values, and you have just combined your two three
column databases into one 5 column database.

HTH,
Bernie
MS Excel MVP