PDA

View Full Version : A Short Formula Please - Excel



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

Bob Phillips
03-25-2011, 04:19 AM
I am sure t is possible, but give us an incentive. Explain the business requirement that needs to be met, and the rules driving that formula.

mdmackillop
03-25-2011, 02:13 PM
You could use a named formula

Paul_Hossler
03-25-2011, 05:33 PM
I'd use a User Defined Function (UDF) just to keep the logic understable

The formula is much too complicated for me (personal style and brain size limitation)

If you decided to use the UDF, the call in D2 could look like

=GetDS (A2, B2, C2)

with all the If's and VLookup in the VBA

My 2 cents

Paul