How do I match up two tables of data with one column in common

I have two data tables and each one has a reference column in common. How do
I match up the two data tables with by that reference column?

Take a look at the VLOOKUP function:-

The VLOOKUP function essentially takes a value that you specify, whether it
be a hardcoded number/letter/text or a value within a cell reference, and
then goes and looks it up in a table. It will look for that value in the
leftmost column of the table, and either find it or the closest match, and
will then return the corresponding value on the same row, in whatever column
of that table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume
it is 25 for example).

You can put a formula in pretty much any other cell, that says, take the
value in cell A1 (25), go and look for it in the lefthand column of the table
(D15), and then when you have found it (or the lowest closest number to
it), go to the second column (or whichever one you specify if there are more
than 2) and give me the number that it is on the same row in that column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the
value in A1 which is 25, then go look for it in the leftmost column (D) of
your specified table (D1:E5), and it will try to find that number. Now it
isn't there, so what it will do is look for the next lower closest number,
which in this case will be 20, and the 2 in the formula says to go and get
the value in the 2nd column (E) in your table, that is on the same row as the
20. That value in this case is 0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result
of the formula change, because now it will either find those numbers or the
lower closest number (and in each case there it is 30), and will subsequently
return 0.35 as the corresponding value.

Just to show you how the 2 really works in that formula, if you added one
more column to your table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simply change
the 2 in the formula to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32
would return 0.45. The one caveat to all of this (When getting the nearest
number is OK) is that the data in your leftmost column must be sorted in
ascending order.


There are times when you would only want it to give you a value if you had
an exact match on the number, and in this instance you would simply add a 4th
argument of 0 or FALSE to the formula, eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column
sorted.

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

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



"Zman" wrote:

I have two data tables and each one has a reference column in common. How do
I match up the two data tables with by that reference column?