A matching problem
 

I'd like to be able to match a name (text) from column B
with an assigned number from a number group range from
Column A (that's also text the way it is entered). What is
my best way? Pivot or formula or something else?

My problem is that:
in Column A I have booklets numbered:
1-50
51-100
101-150
151-200
201-250
251-300

in Column B I have:
John
Bill
Ralph
Ted
Betty
Sara

But then whoever finishes a booklet first gets 301-350 and
so on until the list is completed at the booklet #2951-
3000 so the name list start order mixes up at this point.

My idea is to type in a real number from 1 to 3000 in D1
and get the name of who issued the number in E1.
I could manipulate column A if need be.

Thanks for any ideas!

One try ..

With the set-up as described, and assuming the listings in cols A and B
will be similarly extended down for subsequent booklet numbers
(from 301 till 3000 .. )

Put in the formula bar for E1:

=INDEX(B1:B100,MATCH(D1,--LEFT(A1:A100,SEARCH("-",A1:A100)-1),1))

Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER

--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Les" wrote in message
...
I'd like to be able to match a name (text) from column B
with an assigned number from a number group range from
Column A (that's also text the way it is entered). What is
my best way? Pivot or formula or something else?

My problem is that:
in Column A I have booklets numbered:
1-50
51-100
101-150
151-200
201-250
251-300

in Column B I have:
John
Bill
Ralph
Ted
Betty
Sara

But then whoever finishes a booklet first gets 301-350 and
so on until the list is completed at the booklet #2951-
3000 so the name list start order mixes up at this point.

My idea is to type in a real number from 1 to 3000 in D1
and get the name of who issued the number in E1.
I could manipulate column A if need be.

Thanks for any ideas!

You have saved my day! Thanks
-----Original Message-----
One try ..

With the set-up as described, and assuming the listings=20
in cols A and B
will be similarly extended down for subsequent booklet=20
numbers
(from 301 till 3000 .. )

Put in the formula bar for E1:

=3DINDEX(B1:B100,MATCH(D1,--LEFT(A1:A100,SEARCH("-
",A1:A100)-1),1))

Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER

--=20
Rgds
Max
xl 97
--
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
---
"Les" wrote in=20
message
...
I'd like to be able to match a name (text) from column B
with an assigned number from a number group range from
Column A (that's also text the way it is entered). What=20
is
my best way? Pivot or formula or something else?

My problem is that:
in Column A I have booklets numbered:
1-50
51-100
101-150
151-200
201-250
251-300

in Column B I have:
John
Bill
Ralph
Ted
Betty
Sara

But then whoever finishes a booklet first gets 301-350=20
and
so on until the list is completed at the booklet #2951-
3000 so the name list start order mixes up at this=20
point.

My idea is to type in a real number from 1 to 3000 in D1
and get the name of who issued the number in E1.
I could manipulate column A if need be.

Thanks for any ideas!



.

Glad to hear that !
You're welcome ..
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Les" wrote in message
...
You have saved my day! Thanks