hiya
this is simular to my last post about postcodes infact its the same post
just different question lol
after following max's instructions which were great i found more problems
that was just to much to deal with for what it is.
so i've spent the last 4hours copying postcodes and store codes onto my work
sheet now what i want to do is

if a = m copy n into C
i've tried different if and vlookups but it will only ever do the first 3
lines but i have 1310 lines to do.

the sheet will look like this....

A C M N
wa2 2et 09 wa2 2et 09
cm2 7th 10 w1s 4ht 78
w1s 4ht 78 cm2 7th 10

M and N is the data information A is random once A matches M, C needs to
show N's Details matching M.


thank you


--
deejay

Did you remember to put dollar signs in front of the row numbers of your
lookup table?

Looks like this should work:
=VLookup(M1, A$1:C$1310, 3, 0)

Are there any leading/trailing spaces or other hidden characters? In an
empty cell, test two values that should be equal and see if you get TRUE.
For example:
=A2=M3

If none of that helps, please post the formula you tried as well as the
results. Do you get #N/A error or just incorrect results?

"confused deejay" wrote:

hiya
this is simular to my last post about postcodes infact its the same post
just different question lol
after following max's instructions which were great i found more problems
that was just to much to deal with for what it is.
so i've spent the last 4hours copying postcodes and store codes onto my work
sheet now what i want to do is

if a = m copy n into C
i've tried different if and vlookups but it will only ever do the first 3
lines but i have 1310 lines to do.

the sheet will look like this....

A C M N
wa2 2et 09 wa2 2et 09
cm2 7th 10 w1s 4ht 78
w1s 4ht 78 cm2 7th 10

M and N is the data information A is random once A matches M, C needs to
show N's Details matching M.


thank you


--
deejay

didn't work mate, tried your code (copy,paste) and i think it might have been
backward but as i'm not sure how it works i couldn't change it.
i also tried if with the $ (=if(a1=m$1:M$1300,n$1:n$1300,0) the first line
got the right answer then the rest were blank.

maybe if i explain more...

A (this is pasted in from another sheet) B (is where i want the answer)
cc1 1cc 9
aa2 2aa 7
bb3 3bb 8

column M(has the full list of postcodes) N (code relating to postcode in M)
aa2 2aa 7
bb3 3bb 8
cc1 1cc 9

the list in column A can be in any order and needs to find the correct code
and place it in column B

one code that has half worked is... in B2: =INDEX(N:N,MATCH(LEFT(A2,2),M:M,0))
but that was only using the first 2 letters of the postcode, i need the
whole thing as cc1=9 cc2=4 etc.

hope this is a bit more clear for you mate, hope you can help its kept me
awake for 8 hours now lol

--
deejay


"JMB" wrote:

Did you remember to put dollar signs in front of the row numbers of your
lookup table?

Looks like this should work:
=VLookup(M1, A$1:C$1310, 3, 0)

Are there any leading/trailing spaces or other hidden characters? In an
empty cell, test two values that should be equal and see if you get TRUE.
For example:
=A2=M3

If none of that helps, please post the formula you tried as well as the
results. Do you get #N/A error or just incorrect results?

"confused deejay" wrote:

hiya
this is simular to my last post about postcodes infact its the same post
just different question lol
after following max's instructions which were great i found more problems
that was just to much to deal with for what it is.
so i've spent the last 4hours copying postcodes and store codes onto my work
sheet now what i want to do is

if a = m copy n into C
i've tried different if and vlookups but it will only ever do the first 3
lines but i have 1310 lines to do.

the sheet will look like this....

A C M N
wa2 2et 09 wa2 2et 09
cm2 7th 10 w1s 4ht 78
w1s 4ht 78 cm2 7th 10

M and N is the data information A is random once A matches M, C needs to
show N's Details matching M.


thank you


--
deejay

another one that has worked is =IF(A1=M$1:M$1300,N$1:N$1300,"") but only if
both column A and column M are in the same place.
but column A is in no order and can sometimes be repeated.
--
deejay


"JMB" wrote:

Did you remember to put dollar signs in front of the row numbers of your
lookup table?

Looks like this should work:
=VLookup(M1, A$1:C$1310, 3, 0)

Are there any leading/trailing spaces or other hidden characters? In an
empty cell, test two values that should be equal and see if you get TRUE.
For example:
=A2=M3

If none of that helps, please post the formula you tried as well as the
results. Do you get #N/A error or just incorrect results?

"confused deejay" wrote:

hiya
this is simular to my last post about postcodes infact its the same post
just different question lol
after following max's instructions which were great i found more problems
that was just to much to deal with for what it is.
so i've spent the last 4hours copying postcodes and store codes onto my work
sheet now what i want to do is

if a = m copy n into C
i've tried different if and vlookups but it will only ever do the first 3
lines but i have 1310 lines to do.

the sheet will look like this....

A C M N
wa2 2et 09 wa2 2et 09
cm2 7th 10 w1s 4ht 78
w1s 4ht 78 cm2 7th 10

M and N is the data information A is random once A matches M, C needs to
show N's Details matching M.


thank you


--
deejay

Yep - I think it was backwards. try:
=VLookup(A1, $M$1:$N$1300, 2, 0)



"confused deejay" wrote:

didn't work mate, tried your code (copy,paste) and i think it might have been
backward but as i'm not sure how it works i couldn't change it.
i also tried if with the $ (=if(a1=m$1:M$1300,n$1:n$1300,0) the first line
got the right answer then the rest were blank.

maybe if i explain more...

A (this is pasted in from another sheet) B (is where i want the answer)
cc1 1cc 9
aa2 2aa 7
bb3 3bb 8

column M(has the full list of postcodes) N (code relating to postcode in M)
aa2 2aa 7
bb3 3bb 8
cc1 1cc 9

the list in column A can be in any order and needs to find the correct code
and place it in column B

one code that has half worked is... in B2: =INDEX(N:N,MATCH(LEFT(A2,2),M:M,0))
but that was only using the first 2 letters of the postcode, i need the
whole thing as cc1=9 cc2=4 etc.

hope this is a bit more clear for you mate, hope you can help its kept me
awake for 8 hours now lol

--
deejay


"JMB" wrote:

Did you remember to put dollar signs in front of the row numbers of your
lookup table?

Looks like this should work:
=VLookup(M1, A$1:C$1310, 3, 0)

Are there any leading/trailing spaces or other hidden characters? In an
empty cell, test two values that should be equal and see if you get TRUE.
For example:
=A2=M3

If none of that helps, please post the formula you tried as well as the
results. Do you get #N/A error or just incorrect results?

"confused deejay" wrote:

hiya
this is simular to my last post about postcodes infact its the same post
just different question lol
after following max's instructions which were great i found more problems
that was just to much to deal with for what it is.
so i've spent the last 4hours copying postcodes and store codes onto my work
sheet now what i want to do is

if a = m copy n into C
i've tried different if and vlookups but it will only ever do the first 3
lines but i have 1310 lines to do.

the sheet will look like this....

A C M N
wa2 2et 09 wa2 2et 09
cm2 7th 10 w1s 4ht 78
w1s 4ht 78 cm2 7th 10

M and N is the data information A is random once A matches M, C needs to
show N's Details matching M.


thank you


--
deejay