Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH, LOOKUP, or ???
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH, LOOKUP, or ???
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,"") In article , "Ed" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH, LOOKUP, or ???
Thank you. A slight tweak to one of Mr. Pearson's formulas was just what I
needed. Ed "J.E. McGimpsey" wrote in message ... 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,"") In article , "Ed" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup or match? | Excel Discussion (Misc queries) | |||
lookup and match | Excel Worksheet Functions | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
Match and Lookup | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |