Comparing 2 ranges
Posted by jmilley on August 13, 2001 7:09 AM
I am trying to compare 2 ranges of account numbers and pick up the dollar amount associated with the account numbers in one of the ranges. I have used the vlookup function to pick up matching account numbers but now need to know which accounts are not in the smaller range and therefore are not being picked up in the lookup function. Is there a way to have the vlookup function show which cells it has nothing to match to?
Posted by Mark W. on August 13, 2001 7:15 AM
It's not clear to me if the "smaller" range is the
one with the dollar amounts or what arguments you
are currently using with the VLOOKUP() function.
Assuming the "smaller" range is the one that you're
currently searching for dollar amounts, just
make sure that the 4th (optional) argument to
VLOOKUP() is either 0 or FALSE. If the account
that you're searching for isn't present the in
"smaller" range a #N/A error value will be returned.
Posted by Eric on August 13, 2001 7:24 AM
Reverse what you're looking up
Im using match instead of vlookup, but the basic idea is, rather than looking up the subset values in the superset, try looking for the superset values in the subset to find out which values in the superset are "unique".
The superset data is in col(A) and the subset is in col(B)
In col(C) type the formula
=IF(ISNUMBER(MATCH(A2,B:B,0))=TRUE,"duplicate","unique")
and copy down to cover the entire range of the superset data
should give you a col(C) list of the superset data that is duplicated in the subset or unique to the superset.
Hope that helps
