RE: Comparing Lists

You can use the MATCH function to match the records from the one list against the other.

The answer you will get will be a number. This is a row number from the area you have chosen to match. If you do not get a number, that means that the value was not found, hence giving you your answer.

Here are some details regarding MATCH (you can also click on HELP in Excel to view similar info.)

------------
MATCH(lookup_value,lookup_array,match_type)
------------
Lookup_value is the value you use to find the value you want in a table.

Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

RE: Comparing Lists

Daryl

If you modify your original formula with an ABSOLUTE REFERENCE, before copying it down to the other values, then the range you will be looking into will remain the same.

So if your list is A2:B45
then you need to modify it woth $ signs to look like this

$A$2:$B$45

That locks the column and row references so that it does not change when copied.

I would check the first few results to make sure the formula is working properly before trusting the rest of them.

Regards

Richard