Help with matching by alpha and by number

[kate s]

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


[TABLE="width: 843"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

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 )

Excel Workbook
	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
Leader

 

[Sam_D_Ben]

Peter - Is this MaGIC ?

 

[Peter_SSs]

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)

 

[kate s]

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

 

[Peter_SSs]

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.

 

[kate s]

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!

 

[Peter_SSs]

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.

 

[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!

 

[Peter_SSs]

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.