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

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]suppliers that begin with:[/TD]
[TD]REP[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]JAMIE[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]JAMIE[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]JAMIE[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]DONNA[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]DONNA[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]DONNA[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]JUSTIN[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]JUSTIN[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]JUSTIN[/TD]
[/TR]
</tbody>[/TABLE]




















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
Name	Rep
Excellent Vendor	DONNA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]suppliers t
hat begin with:[/TD]
[TD="bgcolor: #FAFAFA"]REP[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]JAMIE[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA"]JAMIE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA"]JAMIE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA"]DONNA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA"]DONNA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA"]DONNA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]G[/TD]
[TD="bgcolor: #FAFAFA"]JUSTIN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]H[/TD]
[TD="bgcolor: #FAFAFA"]JUSTIN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]I[/TD]
[TD="bgcolor: #FAFAFA"]JUSTIN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=VLOOKUP(LEFT(D3),$A$3:$B$11,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"})

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


Markmzz