Look up, return value

K

Katerinia

i have a list of job codes in Sheet 1 column V that need to be updated with
new codes listed in Sheet 2 column B.

Sheet 2 column A contains the old job code, column b the new one.

How do I reference on Sheet 1 look at column v, find the matching job code
in Sheet 2 column A and return the value in Sheet 2 Column B.
 
T

trip_to_tokyo

EXCEL 2007

I have just put up a file for you at:-

http://www.pierrefondes.com/

Item number 83 (towards the top of the home page at time if posting).

I think that this gives you what you want.

If you agree please hit Yes.

Thanks.
 
T

trip_to_tokyo

EXCEL 2007

This is what I have done:-

1. Sheet1

V1 Old Job Code
V2 A
V3 B
V4 C
V5 D
V6 E


W1 New Job Code
W2 =VLOOKUP(V2,JobCodes,2)
W3 =VLOOKUP(V3,JobCodes,2)
W4 =VLOOKUP(V4,JobCodes,2)
W5 =VLOOKUP(V5,JobCodes,2)
W6 =VLOOKUP(V6,JobCodes,2)

2. Sheet2

A1 Old Job Code
A2 A
A3 B
A4 C
A5 D
A6 E


B1 New Job Code
B2 1
B3 2
B4 3
B5 4
B6 5


Sheet2 cells A 2 to B 6 have a Range Name of JobCodes

Please hit yes if my comments have helped.

Thanks.
 
K

Katerinia

i need to return the value, not the reference.

trip_to_tokyo said:
EXCEL 2007

This is what I have done:-

1. Sheet1

V1 Old Job Code
V2 A
V3 B
V4 C
V5 D
V6 E


W1 New Job Code
W2 =VLOOKUP(V2,JobCodes,2)
W3 =VLOOKUP(V3,JobCodes,2)
W4 =VLOOKUP(V4,JobCodes,2)
W5 =VLOOKUP(V5,JobCodes,2)
W6 =VLOOKUP(V6,JobCodes,2)

2. Sheet2

A1 Old Job Code
A2 A
A3 B
A4 C
A5 D
A6 E


B1 New Job Code
B2 1
B3 2
B4 3
B5 4
B6 5


Sheet2 cells A 2 to B 6 have a Range Name of JobCodes

Please hit yes if my comments have helped.

Thanks.
 
K

Katerinia

Managed to figure it out!
Thanks for your help

=IF(LEN(T2)=8,T2,VLOOKUP(TEXT(T2,0),'JOB CODE REFERENCE
SHEET'!$A$2:$C$2500,3,FALSE))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top