Not Sure Which Function to Use for This
 

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 IDs for our customers. Each
customer can have more than one ship to address, hence the multiple ship to
IDs.

Column B is just a reference list of customer IDs. Its used to match the
old list of customer ship to ids in column A to the new ship to ids 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 theres 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 need to come up with some type of formula/function, because I have over
10,000 old IDs in a list to change into new IDs and don't want to have to
to this line by line.

Thanks for any help or alternative suggestions you may have.

Sorry, I'm using Excel 2003 on WinXP SP2
Thanks,
Damon Johnson

"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 IDs for our customers. Each
customer can have more than one ship to address, hence the multiple ship to
IDs.

Column B is just a reference list of customer IDs. Its used to match the
old list of customer ship to ids in column A to the new ship to ids 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 theres 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 need to come up with some type of formula/function, because I have over
10,000 old IDs in a list to change into new IDs and don't want to have to
to this line by line.

Thanks for any help or alternative suggestions you may have.

One way

Put in D2:

=IF(ISNA(MATCH(A2,B:B,0)),"",INDEX(C:C,MATCH(A2,B: B,0)))

Copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
tada991 wrote in message
...
Sorry, I'm using Excel 2003 on WinXP SP2
Thanks,
Damon Johnson

"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 need to come up with some type of formula/function, because I have
over
10,000 old ID's in a list to change into new ID's and don't want to have
to
to this line by line.

Thanks for any help or alternative suggestions you may have.

Put in D2:
=IF(ISNA(MATCH(A2,B:B,0)),"",INDEX(C:C,MATCH(A2,B: B,0)))

Oops, missed out these lines:

Any IDs in col A unmatched with col B (if any) will return blanks: ""

Its also assumed that col B (ID REF LIST) does not contain any duplicate
items
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

THANKS MAX!!!
That did it! You've just saved me 3 days work!!!!!!
Damon

"Max" wrote:

Put in D2:
=IF(ISNA(MATCH(A2,B:B,0)),"",INDEX(C:C,MATCH(A2,B: B,0)))

Oops, missed out these lines:

Any IDs in col A unmatched with col B (if any) will return blanks: ""

Its also assumed that col B (ID REF LIST) does not contain any duplicate
items
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Glad it worked !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
tada991 wrote in message
...
THANKS MAX!!!
That did it! You've just saved me 3 days work!!!!!!
Damon

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