Help with IF AND formula

[MsMonarch]

Hello

I have a list of suppliers and sales reps that are assigned by Alphabet. For example; any supplier that begins with letters A-C are assigned to JAMIE, in addition to that any suppliers that begin with letters D-F are assigned to DONNA and suppliers that begin with letters G-I are assigned to JUSTIN and so on and so forth. A spreadsheet is provided daily with new suppliers added, I don't want to have to 'assign' each supplier manually because I pull this information into a pivot table by Sales Rep via a Macro.

I can figure out the first logic but can't include the rest in the same formula. Here is my formula:
=IF(LEFT(A3,1) = "A", B3,"NA") This formula works great for the first line but doesn't work if I try and do a range of alpha....like this:
=IF(LEFT(A4,1) = "A""B""C""D""E""F""G""H""I", B4,"NA") or like this: =IF(LEFT(A4,1) = "A-I", B4,"NA")

I have a table set up (as shown below) with alpha assignment

suppliers that begin with:	REP
A	JAMIE
B	JAMIE
C	JAMIE
D	DONNA
E	DONNA
F	DONNA
G	JUSTIN
H	JUSTIN
I	JUSTIN

<tbody>
</tbody>

Can someone help?

 

[markf5998]

You could use something like =IF(OR(LEFT(A1)="a",LEFT(A1)="b"...),"Person1",if(or(=IF(OR(LEFT(A1)="j",LEFT(A1)="k"...),"person2"

Kind of a pain to write it, but it will work.

Other option would be to vlookup the left letter to a table that then assigns to a person.

 

[jtakw]

Hi,

You just need to add the OR function like this:

Excel 2010
	A	B	C
1	suppliers that begin with:	REP
2	A	JAMIE	DONNA
3	B	JAMIE	DONNA
4	C	JAMIE	DONNA
5	D	DONNA	NA
6	E	DONNA	NA
7	F	DONNA	NA
8	G	JUSTIN	NA
9	H	JUSTIN	NA
10	I	JUSTIN	NA
Sheet30

Cell Formulas
Range	Formula
C2	=IF(OR(LEFT(A5,1)={"D","E","F"}),B5,"NA")

 

[KyleX]

If I am understanding correctly and you just want to assign a sales rep to a list of suppliers with the only rule for assignment being the first letter of the supplier name, then I think an index-match would work well here.

=INDEX(B4:B12,MATCH(LEFT(E4,1),A4:A12,0))

Here column E is the incoming supplier list.

 

[Eric W]

Why not something like this?

	A	B	C	D	E
1
2	suppliers t hat begin with:	REP		Name	Rep
3	A	JAMIE		Excellent Vendor	DONNA
4	B	JAMIE
5	C	JAMIE
6	D	DONNA
7	E	DONNA
8	F	DONNA
9	G	JUSTIN
10	H	JUSTIN
11	I	JUSTIN
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E3 =VLOOKUP(LEFT(D3),$A$3:$B$11,2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[markmzz]

Hi!

If I understand what you want, maybe the formulas below can helps.

In F2 and copy down

=LOOKUP(CODE(UPPER(LEFT(D2,1))),{65;68;71;74},{"JAMIE";"DONNA";"JUSTIN";"NA"})

In G2 and copy down

=LOOKUP(CODE(UPPER(LEFT(D2,1))),{65;74},{"JAMIE";"NA"})

	A	B	C	D	E	F	G	H
1	Letter	Code		suppliers that begin with:	REP	Formula1	Formula2
2	A	65		A	JAMIE	JAMIE	JAMIE
3	B	66		B	JAMIE	JAMIE	JAMIE
4	C	67		C	JAMIE	JAMIE	JAMIE
5	D	68		D	DONNA	DONNA	JAMIE
6	E	69		E	DONNA	DONNA	JAMIE
7	F	70		F	DONNA	DONNA	JAMIE
8	G	71		G	JUSTIN	JUSTIN	JAMIE
9	H	72		H	JUSTIN	JUSTIN	JAMIE
10	I	73		I	JUSTIN	JUSTIN	JAMIE
11	J	74		J		NA	NA
12	K	75		K		NA	NA
13	L	76		L		NA	NA
14	M	77		M		NA	NA
15	N	78		N		NA	NA
16	O	79		O		NA	NA
17	P	80		P		NA	NA
18	Q	81		Q		NA	NA
19	R	82		R		NA	NA
20	S	83		S		NA	NA
21	T	84		T		NA	NA
22	U	85		U		NA	NA
23	V	86		V		NA	NA
24	W	87		W		NA	NA
25	X	88		X		NA	NA
26	Y	89		Y		NA	NA
27	Z	90		Z		NA	NA
28
***	******	*****	**	***********************	*******	*********	*********	**

<tbody>
</tbody>

Markmzz