vishwakarma
03-25-2011, 02:35 AM
Hi Guys,
Can anyone in the forum help in creating a simple and short formula in the attached sheet with the highlighted column containing the formula. The attached sheet is the Zip code formula sheet which I use to retrieve the names of the sales reps based on their territory assigned to them.
The Formula in the highlighted column is :-
=IFERROR(IFERROR(IF(C2="Australia",VLOOKUP(A2,V:W,2,0),
IF(ISERROR(VLOOKUP(C2,I:K,3,FALSE))=FALSE,VLOOKUP(C2,I:K,3,FALSE),
IF(VLOOKUP(A2,F:G,2,FALSE)="Unsplit",VLOOKUP(A2,L:M,2,FALSE),
IF(B2="",VLOOKUP(A2,N:O,2,FALSE),
IF(ISERROR(VLOOKUP(CONCATENATE(A2,B2),R:S,2,FALSE)),VLOOKUP(A2,N:O,2,FALSE) ,VLOOKUP(CONCATENATE(A2,B2),R:S,2,FALSE)))))),
IF(AND(C2="Canada",OR(A2="ON",A2="QC")),VLOOKUP(LEFT(B2,2),X:Z,3,0))),IF(C2="Canada",VLOOKUP(A2,AA:AB,2,0)))
Can anyone in the forum help in creating a simple and short formula in the attached sheet with the highlighted column containing the formula. The attached sheet is the Zip code formula sheet which I use to retrieve the names of the sales reps based on their territory assigned to them.
The Formula in the highlighted column is :-
=IFERROR(IFERROR(IF(C2="Australia",VLOOKUP(A2,V:W,2,0),
IF(ISERROR(VLOOKUP(C2,I:K,3,FALSE))=FALSE,VLOOKUP(C2,I:K,3,FALSE),
IF(VLOOKUP(A2,F:G,2,FALSE)="Unsplit",VLOOKUP(A2,L:M,2,FALSE),
IF(B2="",VLOOKUP(A2,N:O,2,FALSE),
IF(ISERROR(VLOOKUP(CONCATENATE(A2,B2),R:S,2,FALSE)),VLOOKUP(A2,N:O,2,FALSE) ,VLOOKUP(CONCATENATE(A2,B2),R:S,2,FALSE)))))),
IF(AND(C2="Canada",OR(A2="ON",A2="QC")),VLOOKUP(LEFT(B2,2),X:Z,3,0))),IF(C2="Canada",VLOOKUP(A2,AA:AB,2,0)))