MATCH, LOOKUP, or ???

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have a list of report numbers in C and a list of file paths which end with
"report number".doc in AA. A formula in AL returns the report number from
the file path. All columns in question are sorted ascending. Now I need to
compare the two lists - original no. (C) vs returned no. (AL) - to determine
which, if any, returned numbers are not included in the original numbers.
Foe example:

Col C Col AA
Col AL
A5-S000123 FilePath\A5-S000123.doc A5-S000123
A5-S000124 FilePath\A5-S000124.doc A5-S000124
A5-S000126 FilePath\A5-S000125.doc A5-S000125
A5-S000127 FilePath\A5-S000126.doc A5-S000126

I'm not sure if this should be a MATCH function, LOOKUP function, or
something else I'm unfamiliar with as yet. I thought of LOOKUP(AL2,
C2:C7000, AA2:AA7000), with the intent that, if AL2 is found within
C2:C7000, then I should get a return of the file path. But if I tried that
with "AL5" in my example above, that value IS found, but at C4, whih would
return the wrong file path.

Any and all suggestions are welcome.

Ed
 
If you just want to flag the ones that don't have a match, check out:

http://www.cpearson.com/excel/duplicat.htm

If you actually want to return the file path, since the value in
column AL is derived from column AA, it seems to me you could use
something like:

=IF(ISNA(MATCH(AL5,C:C,FALSE)),"",AA5)

or

=IF(COUNTIF(C:C,AL5),AA5,"")
 
Back
Top