Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's counter
part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the old
number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the whole
number or even need to type the dashes but yet still have then show up.

I have never used this site before and I may be asking for way to much. If
so please just ignore my request. I thought this would or should be fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike

I forgot to mention that I am using MS Excel 2002. I also have MS Access if
this problem would be better solved there.


"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's
counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then show
up.

I have never used this site before and I may be asking for way to much. If
so please just ignore my request. I thought this would or should be fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike

Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510

the old parcel number of the first row as "1559-F-1"

the new parcel number of "65-4-120-053-0510"

Where is "PT SW" ? Is it part of the old style number? Part of the new style
number?

Biff

"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's
counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then show
up.

I have never used this site before and I may be asking for way to much. If
so please just ignore my request. I thought this would or should be fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike

Sorry, I had them separated but when I posted they ran together for some
reason.

The correct second column of numbers would be in the below sequence with 13
numbers.

65-4-120-053-0510

The first column would be the preceding numbers in front such as 1559-F-1 PT
SW. These could very in length but the 2nd column is consistant with the 13
numbers and dashes.



"Biff" wrote in message
...
Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510

the old parcel number of the first row as "1559-F-1"

the new parcel number of "65-4-120-053-0510"

Where is "PT SW" ? Is it part of the old style number? Part of the new
style number?

Biff

"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first
column is the old style. The second column holds the new style. I would
like to create a form that if you input one or the other of the values
it's counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then show
up.

I have never used this site before and I may be asking for way to much.
If so please just ignore my request. I thought this would or should be
fairly simple and volunteered to do it, but now find myself scratching my
head after several hours at the attempt.

Thanks in advance for any help.

Mike

Just a little more clarification. Their are over 5000 entries in each of
the two columns. Below is the same samples with underscores to show where
one column ends and the other begins. I would be more than happy to send the
whole excel file if that would help. It's size is: 825 KB (845,312 bytes)

1559-F-1 PT SW_______ 65-4-120-053-0510
1559-F-2 PT SW_______ 65-4-120-053-0520
1560-F-1 E 1/2________ 65-4-120-054-0101
1560-F-1 PT SE_______ 65-4-120-054-0120
1560-F-1 PT SE_______ 65-4-120-054-0130
1560-F-2-A LOT_______ 65-4-120-054-0200
1560-F-2-B LOT ______65-4-120-054-0210
1560-F-3 PT SE_______ 65-4-120-054-0220
1560-F PT SE 1/_______ 65-4-120-054-0230
1562-F PT SE 1/_______ 65-4-120-054-0410

Thanks again to anyone who offers a solution.



"Mike" wrote in message
...
Sorry, I had them separated but when I posted they ran together for some
reason.

The correct second column of numbers would be in the below sequence with
13 numbers.

65-4-120-053-0510

The first column would be the preceding numbers in front such as 1559-F-1
PT SW. These could very in length but the 2nd column is consistant with
the 13 numbers and dashes.



"Biff" wrote in message
...
Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510

the old parcel number of the first row as "1559-F-1"

the new parcel number of "65-4-120-053-0510"

Where is "PT SW" ? Is it part of the old style number? Part of the new
style number?

Biff

"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first
column is the old style. The second column holds the new style. I would
like to create a form that if you input one or the other of the values
it's counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then
show up.

I have never used this site before and I may be asking for way to much.
If so please just ignore my request. I thought this would or should be
fairly simple and volunteered to do it, but now find myself scratching
my head after several hours at the attempt.

Thanks in advance for any help.

Mike

Hi!

OK, to answer your basic request:

Assume you enter either the new style or old style ID number in cell A1.
The range of the ID numbers is A3:B5000. Enter this formula in cell B1:

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),VLOOKUP(A1,A3:B5000,2,0))

Biff

"Mike" wrote in message
...
Just a little more clarification. Their are over 5000 entries in each of
the two columns. Below is the same samples with underscores to show where
one column ends and the other begins. I would be more than happy to send
the whole excel file if that would help. It's size is: 825 KB (845,312
bytes)

1559-F-1 PT SW_______ 65-4-120-053-0510
1559-F-2 PT SW_______ 65-4-120-053-0520
1560-F-1 E 1/2________ 65-4-120-054-0101
1560-F-1 PT SE_______ 65-4-120-054-0120
1560-F-1 PT SE_______ 65-4-120-054-0130
1560-F-2-A LOT_______ 65-4-120-054-0200
1560-F-2-B LOT ______65-4-120-054-0210
1560-F-3 PT SE_______ 65-4-120-054-0220
1560-F PT SE 1/_______ 65-4-120-054-0230
1562-F PT SE 1/_______ 65-4-120-054-0410

Thanks again to anyone who offers a solution.



"Mike" wrote in message
...
Sorry, I had them separated but when I posted they ran together for some
reason.

The correct second column of numbers would be in the below sequence with
13 numbers.

65-4-120-053-0510

The first column would be the preceding numbers in front such as 1559-F-1
PT SW. These could very in length but the 2nd column is consistant with
the 13 numbers and dashes.



"Biff" wrote in message
...
Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510

the old parcel number of the first row as "1559-F-1"

the new parcel number of "65-4-120-053-0510"

Where is "PT SW" ? Is it part of the old style number? Part of the new
style number?

Biff

"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first
column is the old style. The second column holds the new style. I would
like to create a form that if you input one or the other of the values
it's counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then
show up.

I have never used this site before and I may be asking for way to much.
If so please just ignore my request. I thought this would or should be
fairly simple and volunteered to do it, but now find myself scratching
my head after several hours at the attempt.

Thanks in advance for any help.

Mike

I can't believe it. That works slick. Looking at that formula I would have
been here till next year scratching my head. Thank you, Thank you.


"Biff" wrote in message
...
Hi!

OK, to answer your basic request:

Assume you enter either the new style or old style ID number in cell A1.
The range of the ID numbers is A3:B5000. Enter this formula in cell B1:

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),VLOOKUP(A1,A3:B5000,2,0))

Biff

"Mike" wrote in message
...
Just a little more clarification. Their are over 5000 entries in each of
the two columns. Below is the same samples with underscores to show where
one column ends and the other begins. I would be more than happy to send
the whole excel file if that would help. It's size is: 825 KB (845,312
bytes)

1559-F-1 PT SW_______ 65-4-120-053-0510
1559-F-2 PT SW_______ 65-4-120-053-0520
1560-F-1 E 1/2________ 65-4-120-054-0101
1560-F-1 PT SE_______ 65-4-120-054-0120
1560-F-1 PT SE_______ 65-4-120-054-0130
1560-F-2-A LOT_______ 65-4-120-054-0200
1560-F-2-B LOT ______65-4-120-054-0210
1560-F-3 PT SE_______ 65-4-120-054-0220
1560-F PT SE 1/_______ 65-4-120-054-0230
1562-F PT SE 1/_______ 65-4-120-054-0410

Thanks again to anyone who offers a solution.



"Mike" wrote in message
...
Sorry, I had them separated but when I posted they ran together for some
reason.

The correct second column of numbers would be in the below sequence with
13 numbers.

65-4-120-053-0510

The first column would be the preceding numbers in front such as
1559-F-1 PT SW. These could very in length but the 2nd column is
consistant with the 13 numbers and dashes.



"Biff" wrote in message
...
Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510

the old parcel number of the first row as "1559-F-1"

the new parcel number of "65-4-120-053-0510"

Where is "PT SW" ? Is it part of the old style number? Part of the new
style number?

Biff

"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like
to cross reference with each other. They are parcel numbers. The first
column is the old style. The second column holds the new style. I
would like to create a form that if you input one or the other of the
values it's counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1"
I would like it's equivalent "65-4-120-053-0510" to show up. Or switch
it around and type in the new parcel number of "65-4-120-053-0510" and
the old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then
show up.

I have never used this site before and I may be asking for way to
much. If so please just ignore my request. I thought this would or
should be fairly simple and volunteered to do it, but now find myself
scratching my head after several hours at the attempt.

Thanks in advance for any help.

Mike

Looking at that formula I would have been here till next year scratching my
head.

Some folks call that job security!

Thanks for the feedback.

Biff

"Mike" wrote in message
...
I can't believe it. That works slick. Looking at that formula I would have
been here till next year scratching my head. Thank you, Thank you.


"Biff" wrote in message
...
Hi!

OK, to answer your basic request:

Assume you enter either the new style or old style ID number in cell A1.
The range of the ID numbers is A3:B5000. Enter this formula in cell B1:

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),VLOOKUP(A1,A3:B5000,2,0))

Biff

"Mike" wrote in message
...
Just a little more clarification. Their are over 5000 entries in each
of the two columns. Below is the same samples with underscores to show
where one column ends and the other begins. I would be more than happy
to send the whole excel file if that would help. It's size is: 825 KB
(845,312 bytes)

1559-F-1 PT SW_______ 65-4-120-053-0510
1559-F-2 PT SW_______ 65-4-120-053-0520
1560-F-1 E 1/2________ 65-4-120-054-0101
1560-F-1 PT SE_______ 65-4-120-054-0120
1560-F-1 PT SE_______ 65-4-120-054-0130
1560-F-2-A LOT_______ 65-4-120-054-0200
1560-F-2-B LOT ______65-4-120-054-0210
1560-F-3 PT SE_______ 65-4-120-054-0220
1560-F PT SE 1/_______ 65-4-120-054-0230
1562-F PT SE 1/_______ 65-4-120-054-0410

Thanks again to anyone who offers a solution.



"Mike" wrote in message
...
Sorry, I had them separated but when I posted they ran together for
some reason.

The correct second column of numbers would be in the below sequence
with 13 numbers.

65-4-120-053-0510

The first column would be the preceding numbers in front such as
1559-F-1 PT SW. These could very in length but the 2nd column is
consistant with the 13 numbers and dashes.



"Biff" wrote in message
...
Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510

the old parcel number of the first row as "1559-F-1"

the new parcel number of "65-4-120-053-0510"

Where is "PT SW" ? Is it part of the old style number? Part of the new
style number?

Biff

"Mike" wrote in message
...
Hi

Hope someone can help. I have two sets of numbers that I would like
to cross reference with each other. They are parcel numbers. The
first column is the old style. The second column holds the new style.
I would like to create a form that if you input one or the other of
the values it's counter part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1"
I would like it's equivalent "65-4-120-053-0510" to show up. Or
switch it around and type in the new parcel number of
"65-4-120-053-0510" and the old number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole number or even need to type the dashes but yet still have then
show up.

I have never used this site before and I may be asking for way to
much. If so please just ignore my request. I thought this would or
should be fairly simple and volunteered to do it, but now find myself
scratching my head after several hours at the attempt.

Thanks in advance for any help.

Mike

Biff's equation is nice.

for the second part of your question
for the three basic numbers would there be duplicates in the XXX-XXXX portion?
if no,
you could type in XXX-XXXX and change Biff's equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&A1, A3:A5000,0))

to get rid of the last Hyphen you could enter "XXXXXXX"
and change the equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&Lef tA1,3)&"-"&right(A1,4),A3:A5000,0))

If there are duplicates the equation is more complex but you could enter
YXXXXXXX
where Y is the 5 6 or 7
and further change the formula to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("6"&lef t(A1,1)&"-4-120-"&mid(A1,2,3)&"-"&right(A1,4),A3:A5000,0))

neddless to say try them out on a copy of your file. I am notorius for typos.

"Mike" wrote:

Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's counter
part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the old
number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the whole
number or even need to type the dashes but yet still have then show up.

I have never used this site before and I may be asking for way to much. If
so please just ignore my request. I thought this would or should be fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike

Can't believe all the great help I'm getting here. My first experience with
a newsgroup and I can't say enough good about it. I won't get a chance to
try this out till late this eve, but will sure do so then. Thanks. I'll let
you know what happens.




"bj" wrote in message
...
Biff's equation is nice.

for the second part of your question
for the three basic numbers would there be duplicates in the XXX-XXXX
portion?
if no,
you could type in XXX-XXXX and change Biff's equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&A1, A3:A5000,0))

to get rid of the last Hyphen you could enter "XXXXXXX"
and change the equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&Lef tA1,3)&"-"&right(A1,4),A3:A5000,0))

If there are duplicates the equation is more complex but you could enter
YXXXXXXX
where Y is the 5 6 or 7
and further change the formula to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("6"&lef t(A1,1)&"-4-120-"&mid(A1,2,3)&"-"&right(A1,4),A3:A5000,0))

neddless to say try them out on a copy of your file. I am notorius for
typos.

"Mike" wrote:

Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first
column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's
counter
part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old
number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole
number or even need to type the dashes but yet still have then show up.

I have never used this site before and I may be asking for way to much.
If
so please just ignore my request. I thought this would or should be
fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike

Sorry, I think my brain is fried. Can't seem to make any of these work.
Here is what I have now. I changed some of the numbers in the formula to
actual cell ranges that are being used. I may have messed it up.

I am inputting numbers into cell A1. A text explaination in cell B1
A text explaination in cell A2 and the below formula into cell B2.
My range of column numbers start in cell A3 with 65-4-120-011-0101
Column B3 starts with 8-F ALL THAT PT

Here is the formula that I am trying. I've checked it over several times but
that doesn't mean anything.
This is the formula you suggested as:
" to get rid of the last Hyphen you could enter "XXXXXXX" and change the
equation to"


=IF(ISNA(VLOOKUP(A1,A3:B5949,2,0)),INDEX(A3:A5949, MATCH(A1,B3:B5949,0)),INDEX(B3:B5949,MATCH("*"&Lef tA1,3)&"-"&right(A1,4),A3:A5949,0))

As before I can't thank you enough. If you would rather not deal with this
it's OK because the other fix from Biff will work fine. I don't want to wear
out my welcome here. I think this forum will be an invaluable help for me.

Below are the actual numbers used in columns A and B for the first 20 rows.

Mike

Column A
65-4-120-011-0101
65-4-120-011-0105
65-4-120-011-0120
65-4-120-011-0205
65-4-120-011-0210
65-4-120-011-0300
65-4-120-011-0310
65-4-120-011-0320
65-4-120-011-0330
65-4-120-011-0340

65-4-120-011-0350
65-4-120-011-0360
65-4-120-011-0370
65-4-120-011-0380
65-4-120-011-0390
65-4-120-011-0400
65-4-120-011-0410
65-4-120-011-0420
65-4-120-011-0430
65-4-120-011-0440


Column B
8-F ALL THAT PT
8-F PT OF NE 1
3-F-1 PT NE 1/4
3-F W 1/2 NE 1/
3-F CSM #2106 D
9-F-1 PT E 1/2
9-F-3-P PT E 1/
9-F-3-D PT NE 1
9-F-3-E PT NE 1
9-F-3-H PT E 1/
9-F-3-O PT E 1/
9-F-3-N PT E 1/
9-F-2 PT NE 1/4
9-F-3-M PT NE 1
9-F-3-L PT NE 1
9-F-3-K PT NE 1
9-F-3-J PT NE 1
9-F-3-Q PT NE 1
9-F-3-G PT E 1/
9-F-3-B PT NE 1







"Mike" wrote in message
...
Can't believe all the great help I'm getting here. My first experience
with a newsgroup and I can't say enough good about it. I won't get a
chance to try this out till late this eve, but will sure do so then.
Thanks. I'll let you know what happens.




"bj" wrote in message
...
Biff's equation is nice.

for the second part of your question
for the three basic numbers would there be duplicates in the XXX-XXXX
portion?
if no,
you could type in XXX-XXXX and change Biff's equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&A1, A3:A5000,0))

to get rid of the last Hyphen you could enter "XXXXXXX"
and change the equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&Lef tA1,3)&"-"&right(A1,4),A3:A5000,0))

If there are duplicates the equation is more complex but you could enter
YXXXXXXX
where Y is the 5 6 or 7
and further change the formula to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000, MATCH(A1,B3:B5000,0)),index(B3:B5000,match("6"&lef t(A1,1)&"-4-120-"&mid(A1,2,3)&"-"&right(A1,4),A3:A5000,0))

neddless to say try them out on a copy of your file. I am notorius for
typos.

"Mike" wrote:

Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first
column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's
counter
part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the
old
number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the
whole
number or even need to type the dashes but yet still have then show up.

I have never used this site before and I may be asking for way to much.
If
so please just ignore my request. I thought this would or should be
fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike