# Thread: Help with matching by alpha and by number Thanks: 0 Likes: 0

1. ## Help with matching by alpha and by number

Hello, Excel friends. I am trying to wrap my head around how to match by alpha and by number. I have below an example of what I'm going for.

Example: Meeting group discussions with one group leader for every room number. People assigned to room one with the last name between a-ch will have Wayne Barron as their group leadder. People assigned to room two with last name between d-he will have Urich Elms as their group leader, etc.

I will have about 50 to 75 people assigned with a room. I need a print out so they will know who their group leader will be. I am forced to work with a Excel 10 on client's computer. I'm a novice, so this is a challenge. I tried index match but I'm faced with the alphabetical system they insist on with various length a-ch, mc-rote, etc. I tried doing a helper cell with =length, but I'm a bit at a loss how to proceed.

Thank you for the help. Much appreciated! I can of course split up the a-ch, ci-gi into separate cells and get rid of the dash. I just don't know where to go from there.

Room Barron Elms Johnson Heller
1 a-ch ci-gi go-ma mc-r
2 a-c d-he hi-ma mc-rote
3 a-cra de-he hi-lo lu-r

2. ## Re: Help with matching by alpha and by number

Welcome to the MrExcel board!

Assumptions
- You will have more 'Leader columns' to cover the entire alphabet in your real data
- You will have ensured that the break-up of the alphabet across each row does not exclude any possible names. For example, with your sample, Don Davis or Jan Cryller would have no leader if assigned to room 3 (though my formula would give them a leader.

Anyway see if this is any use for you. Post back with examples if you find some that this does not work for (I'm not over-confident that this will have covered all bases )

 A B C D E F G H I J K 1 Room Barron Elms Johnson Heller First name Last Name Room Leader 2 1 a-ch ci-gi go-ma mc-r Simon Jones 1 Johnson 3 2a a-c d-he hi-ma mc-rote Ann Allen 3 Barron 4 3 a-cra de-he hi-lo lu-r Ken Heavener 2a Elms 5 Don Davis 3 Barron 6 Jen Rainsford 2a Heller

 Cell Formula K2 =LOOKUP(I2,INDEX(B\$2:E\$4,MATCH(J2,A\$2:A\$4,0),0),B\$1:E\$1)

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Help with matching by alpha and by number

Peter - Is this MaGIC ?

4. ## Re: Help with matching by alpha and by number

Originally Posted by Sam_D_Ben
Peter - Is this MaGIC ?
No, in fact it is wrong as it puts Ahn Go room 1 with leader Elms when it should be Johnson.
Next try is

=LOOKUP(I2&"z",INDEX(B\$2:E\$4,MATCH(J2,A\$2:A\$4,0),0),B\$1:E\$1)

5. ## Re: Help with matching by alpha and by number

Wow, thank you so much! I am sitting here just studying your examples. I will test it tonight and report back.

6. ## Re: Help with matching by alpha and by number

Originally Posted by kate s
Wow, thank you so much! I am sitting here just studying your examples. I will test it tonight and report back.
No problem. Let us know of any problems.

7. ## Re: Help with matching by alpha and by number

What does &"z" mean? I am assuming it is a wild card. I can't get it figured out yet, but I'm trying to break down the components. Might be that this is just too advanced for my level. That's okay, I will keep working at it. Thank you!

8. ## Re: Help with matching by alpha and by number

Originally Posted by kate s
What does &"z" mean? I am assuming it is a wild card.
No, it isn't a wild card. It is to avoid the situation identified in post 4 where a last name might happen to exactly equal the characters to the left of the "-" in one of your character ranges. In that example the last name is "Go" and for room 1 you have a character range "go-ma".
Alphabetically "Go" comes before "go-ma" so this person would be assigned to the previous leader. By adding a "z" to their name for the lookup, it becomes "Goz" which alphabetically comes after "go-ma" so they get assigned to the correct leader.

9. ## Re: Help with matching by alpha and by number

I've been working for a few hours after work each night trying to sort the spreadsheet I was making. Today I got it to work!!!

Thank you SO much for your help. I kept studying your example and finally I fiddled enough. I just tested and tested and so far it is error free!

I realize I love Excel! I love it more than watching television actually! Working through this has taught me a lot.

Thank you again. I'll post my next question on another thread!

10. ## Re: Help with matching by alpha and by number

Originally Posted by kate s
I've been working for a few hours after work each night trying to sort the spreadsheet I was making. Today I got it to work!!!

Thank you SO much for your help. I kept studying your example and finally I fiddled enough. I just tested and tested and so far it is error free!

I realize I love Excel! I love it more than watching television actually! Working through this has taught me a lot.

Thank you again. I'll post my next question on another thread!
I've been away for a while but I'm glad you got it sorted.

You're welcome. And if you want more help with your other question, you would need to give a bit more specific detail about just what you have and what you are trying to achieve, as well as what version of Excel you do have.