A matching problem

[Les]

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!

 

[Max]

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

 

[Les]

You have saved my day! Thanks

-----Original Message-----
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 <at>yahoo<dot>com
---



.

 

[Max]

Glad to hear that !
You're welcome ..