mathcing and comparing

[derekc]

i have 2 spreadsheets with 5 column of data on each, on one workbook, w
will call booka,in column c there are part numbers and column f i
quantity. what im trying to do is take the parts numbers and match the
to another set of part numbers on the second book(bookb), and paste th
quantity of that part number next to the qauntity on bookb which i
column as well. i dont need the part numbers to show, im just trying o
match the quantities next to each other and the part numbers arent i
order. so if the way i described is easy or another way is easier.
tried multiple things but just can seem to get it to work. also some o
the part numbers arent even there so those will be skipped. also,. som
of the part numbers dont match, which it would be nice if those can b
highlighted or something, or anything that makes them stand out. bu
basically i just want the first function, mathcing the part numbers t
compare quantities. thanks for any help

 

[Bernie Deitrick]

derek,

In BookA, in cell G1, use a formula like:

=IF(ISERROR(MATCH(C1,[BookB.xls]Sheet1!$C:$C,FALSE)),"No
Match",INDEX([BookB.xls]Sheet1!$F:$F,MATCH(C1,[BookB.xls]Sheet1!$C:$C,FALSE)
,1))

You will need to adjust the sheet names as well.

HTH,
Bernie
MS Excel MVP

 

[derekc]

any help or a step in a good direction, im a novice but i cant stil
learn if im given a good direction to start in

 

[Tom Ogilvy]

=Vlookup(A1,'[booka.xls]Sheet1'!$C$1:$F$500,4,False)

would be in B1 in Bookb assuming A1 holds the first part number to be looked
up.

Then drag fill the formula down the column.

 

[derekc]

im about to eat lunch, i will try both and get back to both of you a
soon as i can. thanks a bunch

 

[Tom Ogilvy]

vlookup assumes the value to match is in the leftmost column of the range.
This fits your criteria, so there is no reason to use Index and Match which
are equivalent, but don't have this restriction. Bernie has added an
additional test to see if there will be a match and thus suppress the #N/A
which is returned by default. You can prepend that test to the vlookup as
well if you want that.

 

[derekc]

i tried bernies, it came no match everytime , even if there was
match.

toms gave me a na everytime, this thing hasbeen a pain

 

[derekc]

bum

 

[Tom Ogilvy]

Vlookup is pretty basic. #N/A means it didn't find a match.

Note that the string 123 does not match the number 123.

I don't know if that has any bearing on your problem.