Not Sure Which Function to Use for This

[Guest]

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.

 

[Guest]

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

 

[Max]

One way

Put in D2:

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

Copy down

 

[Max]

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

 

[Guest]

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

 

[Max]

Glad it worked !
Thanks for the feedback

 

[Ron Rosenfeld]

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