MATCH, LOOKUP, or ???

[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

 

[J.E. McGimpsey]

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,"")

 

[Ed]

Thank you. A slight tweak to one of Mr. Pearson's formulas was just what I
needed.

Ed