Ok. I'm sure there is a way to do this but I don't want to spend all
afternoon figuring it out. I've got two Excel sheets and they have a
common column which is the ISBN numbers. What I want to do is take all
the info in one spread and match it according to the row of the same
ISBN in the other spread sheet. So basically there's a common variable
number on each sheet and I just want to combine the data I have on one
sheet with the data I have on the other by matching the ISBNs. Let me
try to make it a little clearer:

Spread Sheet 1:

Column A   Column B  ETC.
ISBN       TITLE     
1559276908 Emotional Alchemy

Spread Sheet 2:

Column A   Column B
ISBN       DESCRIPTION 
1559276908 Alchemists sought to transform lead into gold...

I want to combine the spread sheets so they're like this:

Column A   Column B            Column C
ISBN       TITLE               DESCRIPTION 
1559276908 Emotional Alchemy   Alchemists sought to transform lead into gold...

But to do this in any sort of manually matching according to ISBN
would be extremely time consuming. Please tell me how to do this in a
simple and automated fashion.

---

Request for Question Clarification by answerguru-ga on 16 Dec 2005 16:54 PST

Hi there,

This can be done fairly easily using Access - would instructions along
those lines be acceptable as an answer?

Thanks,
answerguru-ga

---

Clarification of Question by jpbischke-ga on 16 Dec 2005 17:46 PST

Yeah. I have Access as well. You can let me know how to do it with that.

This is easy one.  You should not pay $20 for this one. Sorry google
guys. Use the vlookup function.  Go to that sheet 1 to cell C2 and put
this in there =vlookup(+A2,Sheet2!A2:B2,2,FALSE).  Don't forget to put
the false in there.

Just check out the vlookup function.

I'm sorry. You're going to have to walk me through it a little more
than that. My Excel skills are not very advanced and I have no idea
what vlookup is or even how to perform a function necessarily. That's
why this is a $20 question. I pasted this
"=vlookup(+A2,Sheet2!A2:B2,2,FALSE)" in C2 of sheet 1 and it didn't do
anything.

Hi

Vlookup does not work.

You should use MATCH function. Make sure you add 0 as Match type.

e-mail me your spreadsheet and I will send it back.


Returns the relative position of an item in an array that matches a
specified value in a specified order. Use MATCH instead of one of the
LOOKUP functions when you need the position of an item in a range
instead of the item itself.
Syntax
MATCH(lookup_value,lookup_array,match_type)
Lookup_value   is the value you use to find the value you want in a table. 
?  Lookup_value is the value you want to match in lookup_array. For
example, when you look up someone's number in a telephone book, you
are using the person's name as the lookup value, but the telephone
number is the value you want.
?  Lookup_value can be a value (number, text, or logical value) or a
cell reference to a number, text, or logical value.
Lookup_array   is a contiguous range of cells containing possible
lookup values. Lookup_array can be an array or an array reference.
Match_type   is the number -1, 0, or 1. Match_type specifies how
Microsoft Excel matches lookup_value with values in lookup_array.
?  If match_type is 1, MATCH finds the largest value that is less than
or equal to lookup_value. Lookup_array must be placed in ascending
order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
?  If match_type is 0, MATCH finds the first value that is exactly
equal to lookup_value. Lookup_array can be in any order.
?  If match_type is -1, MATCH finds the smallest value that is greater
than or equal to lookup_value. Lookup_array must be placed in
descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
?  If match_type is omitted, it is assumed to be 1.
Remarks 
?  MATCH returns the position of the matched value within
lookup_array, not the value itself. For example,
MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b"
within the array {"a","b","c"}.
?  MATCH does not distinguish between uppercase and lowercase letters
when matching text values.
?  If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
?  If match_type is 0 and lookup_value is text, lookup_value can
contain the wildcard characters asterisk (*) and question mark (?). An
asterisk matches any sequence of characters; a question mark matches
any single character.

how to do it

Lets assume your data in Sheet1 is from A2 to A500
and in sheet2 from A2 to A600

In Sheet1 in column B2 write and then copy and paste it to B2 to B500
This will give you the LINE NUMBER
=MATCH(A2,Sheet1!$A$2:$A$500,0)
In Column C write
=INDEX(Sheet1!$A$2:$A$500,b2,1)
Which gives you the ISBN of Sheet 1

There is more than one way to skin a cat in excel.  Vlookup -
Index/match.  They both will do what he wants.  I always liked vlookup
since it is one function not two.

Here is an article discussing how to use both. 
http://www.mrexcel.com/tip021.shtml

Don't get me wrong Index/match has its advantages.  But for someone
who is new it can be confusing.

jpbischke-ga, I had both spreadsheets in the same file as 2 sheets
Sheet1 & Sheet2.  That is how I set my example up.  You probably have
two different files.  You can do it either way.  You just need to
reference the sheets & files correctly.

Try this, if you like, 

1.  Go to your Spread Sheet 1 (I am assuming the cell A1 has the
"ISBN" in your file. )

2.  Go to cell C2 and type 

=vlookup(

3.  Now use you mouse and click on cell A2 in that file.  (It should
have 1559276908 in it.)

4. type a comma  
, 

5.  Now use you mouse again and find your Spread Sheet 2 and click and
hold on cell A2 (which should be 1559276908)and drag across to cell B2
(which should have "Alchemists sought to transform lead into gold"  in
it.  Unclick the mouse

6. Now type 
,2,false)

7.  Hit enter

That is it.  You have to make sure the lookup names of both files are
same format.  One is not text and the other is general.  That can burn
you sometimes.

Copy that cell down and you are done. 

Paul

Did you figure it out?

Sorry I am just not getting this. My function skills are amateur. I
was able to do what excelassistant said but it's not the ISBN that I
want copied over from the other sheet. I already have that as the
common variable. What I want is the description that is next to that
ISBN from the other sheet. I think this requires a somewhat complex
function and it'd be best if I just sent someone the Excel sheets and
they created the function and I could hopefully deduce from their
function what I need to do in the case that I need to do this in the
future.

What's your email excelassistant or anyone that thinks they can do
this and then adequately explain what they did so I can make the
function work as well?