Returing a vaule based on the first letter of another cell

[Petteri]

Hello Everyone!

I'm working on a project where I need to return a set value (1,2,3 etc.) based on the first letter of another cell. However there is an additional wrinkle to this. The letters are organzined into certain groups. For example the letters A,B,C are always in one goup. I need to be able to define this group and then be able to set this group the set value.

Groups: (A,B,C) = 1, (D,E,F,G,H) = 2, (I, J,K,L) =3, (M, N, O) = 1 etc....

Example:

City Code	To carousel
AUS</SPAN>	1</SPAN>
BDL</SPAN>	1</SPAN>
BOG</SPAN>	1</SPAN>
BOS</SPAN>	1</SPAN>
CUN</SPAN>	1</SPAN>
DCA</SPAN>	1</SPAN>
EWR</SPAN>	1</SPAN>
HPN</SPAN>	</SPAN>1

<TBODY>
</TBODY>

I hope this makes sense, so I need the To Carousel value to fill in the correct value depeding on the first letter of the City Code column.

Thanks for any help!!

 

[Aladin Akyurek]

Create a 2 column table of correspondences on a sheet named Admin like below...

A2: A, B, C
A3: D, E, F, G, H
...

B2: 1
B3: 2
...

Select this range and name the selection as CTable (from correspondence table).

Now invoke in B2 (under the header To carousel)...

=LOOKUP(9.99999999999999E+307,S
  EARCH(LEFT(A2),","&SUBSTITUTE(INDEX(CTable,0,1)," ","")),
  INDEX(CTable,0,2))

 

[Petteri]

EDIT: my mistake there were some unwanted spaces in the formula. It's now working!!

Thank you so much for the answer. I'm very new at Excel. I've done the above but I'm getting a #NAME error.

Destination</SPAN>	Number of flights</SPAN>	Carousel Assigned</SPAN>
AUS</SPAN>	1</SPAN>	#NAME?</SPAN>
BDL</SPAN>	1</SPAN>	1</SPAN>
BOG</SPAN>	1</SPAN>	1</SPAN>
BOS</SPAN>	3</SPAN>	1</SPAN>
CUN</SPAN>	1</SPAN>	1</SPAN>

<TBODY>
</TBODY>

CTable:

A, B, C</SPAN>	1</SPAN>
D,E, F, G, H</SPAN>	1</SPAN>
I, J, K, L</SPAN>	2</SPAN>
M, N, O</SPAN>	1</SPAN>
P, Q, R</SPAN>	3</SPAN>
S</SPAN>	3</SPAN>
T, U, V, W, X, Y, Z</SPAN>	3</SPAN>

<TBODY>
</TBODY>

Formula used: =LOOKUP(9.99999999999999E+307,S
EARCH(LEFT(A2),","&SUBSTITUTE(INDEX(CTable,0,1)," ","")),
INDEX(CTable,0,2))

Thanks again! I'm not sure what I've done wrong....

 

[Aladin Akyurek]

@Petteri

You are welcome.

 

[Petteri]

Ok, I've got one last question reguarding this sheet. If you don't mind, it's certainly appreciated!

I now need to total some values based on the carousel assigned:

Destination</SPAN>	Number of flights</SPAN>	Carousel Assigned</SPAN>	Number of seats</SPAN>
AUS</SPAN>	1</SPAN>	1</SPAN>	150</SPAN>
BDL</SPAN>	1</SPAN>	1</SPAN>	150</SPAN>
BOG</SPAN>	1</SPAN>	1</SPAN>	150</SPAN>
BOS</SPAN>	3</SPAN>	1</SPAN>	450</SPAN>
CUN</SPAN>	1</SPAN>	1</SPAN>	150</SPAN>
			1050</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

Carousel 1 Daily Total</SPAN>	2750</SPAN>
Carousel 2 Daily Total</SPAN>	2630</SPAN>
Carousel 3 Daily Total</SPAN>	2150</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

I'm currently using this, but it's just a manual process to define the cells and won't auto update: =SUM(D7,D11, D22)

 

[Aladin Akyurek]

Have a look at the SUMIF function.