On Mon, 17 Jan 2005 07:57:06 -0800, "tada991"
wrote:

Hello Everyone,
I'm not sure what function to use to accomplish this;

I have 4 columns;
A B C D
OLD ID ID REF LIST NEW ID RETURN VALUE
01224 01224 1 1
01225 01225 2 2
01226 01226 3 3
01227 01227 4 4
01227 01228 5 4
01227 01229 6 4
01228 01230 7 5
01228 01231 8 5
01229 01232 9 6
Column A is an old list of ship to address ID’s for our customers. Each
customer can have more than one ship to address, hence the multiple ship to
ID’s.

Column B is just a reference list of customer ID’s. It’s used to match the
old list of customer ship to id’s in column A to the new ship to id’s in
column C. Example; old id 01224 is now new id 1, old id 01225 is now new id 2
and so on.

What I need is a formula/function that looks at the numbers in column A and
then determines if there’s a matching number anywhere in column B. If the
value in A1 = a value in B1:B5, then I want the formula to return the
matching value in column C to column D.(see example above for desired result)


I don't understand your description of what you want. I am having trouble
reconciling

"if there’s a matching number anywhere in column B"

with

"If the value in A1 = a value in B1:B5"

I'm not sure about the significance of B1:B5.

If the B1:B5 was a misstatement, and you really want to generate a value based
on the OLD ID appearing anywhere in the ID REF LIST column, then, assuming your
values are in columns A:C, the formula:

=VLOOKUP(A2,$B:$C,2,0)

entered in D2 and copy/dragged down as far as necessary would seem to do what
you describe. You may shorten the range reference for the table to something
like $B$2:$C$10001 (encompassing only your 10,000 entries) if the formula above
runs too slowly or if have possibly confounding data below the last table
entry.

The formula will return #N/A if there is no matching entry in Column B.


--ron