hi
i have a worksheet with postcodes on it in column A in column m i have a
list of short postcodes with a number in the adjacent column i.e

A M N
CM0 7DQ CM 89
WA12 2ER WA 01

this can be repeated several times and can be in any order.
what i need is in column B i want the matching code from column N

I thought of =IF A:A=M:M,N:N but i think this is confusing things plus its
not reconignising the part code in M. because A can be in any order and
duplicated many times its hard to add the IF to the line number.


p.s using 2003 if this helps

Thank YOU
--
deejay

Data assumed in row2 down
Try in B2: =INDEX(N:N,MATCH(LEFT(A2,2),M:M,0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---
"confused deejay" wrote:
i have a worksheet with postcodes on it in column A in column m i have a
list of short postcodes with a number in the adjacent column i.e

A M N
CM0 7DQ CM 89
WA12 2ER WA 01

this can be repeated several times and can be in any order.
what i need is in column B i want the matching code from column N

I thought of =IF A:A=M:M,N:N but i think this is confusing things plus its
not reconignising the part code in M. because A can be in any order and
duplicated many times its hard to add the IF to the line number.

hiya max
thank you for your response works perfect on postcodes with 2 letters but
not single ones i.e

cm0 8rg works perfect
w3 9qh shows N/A

:( any more ideas my friend
--
deejay


"Max" wrote:

Data assumed in row2 down
Try in B2: =INDEX(N:N,MATCH(LEFT(A2,2),M:M,0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---
"confused deejay" wrote:
i have a worksheet with postcodes on it in column A in column m i have a
list of short postcodes with a number in the adjacent column i.e

A M N
CM0 7DQ CM 89
WA12 2ER WA 01

this can be repeated several times and can be in any order.
what i need is in column B i want the matching code from column N

I thought of =IF A:A=M:M,N:N but i think this is confusing things plus its
not reconignising the part code in M. because A can be in any order and
duplicated many times its hard to add the IF to the line number.

thank you for your response works perfect on postcodes with 2 letters
Yup, that earlier response answers it on the face of your original post.
Press the "Yes" button for that response, won't you?

.. but not single ones
Hey, this is a new query altogether. One thought to pursue is to keep it
simple: Separate your reference list in cols M & N into 2 lists by single &
double letters. Then structure it up likewise in another col, eg let's say
you now have the single letter ref lists in cols O & P (so cols M & N would
now house only the double letter codes).

Use in say C2, copied down:
=INDEX(P:P,MATCH(LEFT(A2),O:O,0))
to get it for the single letters

Then "tie"* the returns from the 2 formulated cols B & C in say, col D
In D2, copied down:
=INDEX(B2:C2,MATCH(TRUE,INDEX((ISNUMBER(B2:C2)),), 0))
*an intentional sequential checking of 2 letter codes ahead of single letter
codes
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---