# Returing a vaule based on the first letter of another cell

#### Petteri

##### New Member
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 1 BDL 1 BOG 1 BOS 1 CUN 1 DCA 1 EWR 1 HPN 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!!

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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)...
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,S
EARCH(LEFT(A2),","&SUBSTITUTE(INDEX(CTable,0,1)," ","")),
INDEX(CTable,0,2))
``````

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 Number of flights Carousel Assigned AUS 1 #NAME? BDL 1 1 BOG 1 1 BOS 3 1 CUN 1 1

<TBODY>
</TBODY>

CTable:

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

<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....

Last edited:
@Petteri

You are welcome.

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 Number of flights Carousel Assigned Number of seats AUS 1 1 150 BDL 1 1 150 BOG 1 1 150 BOS 3 1 450 CUN 1 1 150 1050

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

 Carousel 1 Daily Total 2750 Carousel 2 Daily Total 2630 Carousel 3 Daily Total 2150

<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)

Have a look at the SUMIF function.

Replies
4
Views
3K
Replies
8
Views
991
Replies
0
Views
1K
Replies
9
Views
1K
Replies
9
Views
659

1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back