Speed up / simplify this Excel formula

[Ajain]

Hello Everyone,


So I started writing a formula and ended up with the one below. It looks complex but works. The only issue I am facing is that it is extremely slow and takes forever to calculate. I know this might be due to all the IF statements that I am using, but then other than going in for a VBA solution is there anything that can be done to speed up the processing?

[pre]

=IF(ISERROR(IF(countryLink="United States",VLOOKUP(fl,USpp,2,0),IF(countryLink="United Kingdom",
VLOOKUP(fl,UKpp,2,0),IF(countryLink="Germany",VLOOKUP(fl,DEpp,2,0),IF(countryLink="Australia",
VLOOKUP(fl,AUpp,2,0),IF(countryLink="Canada",VLOOKUP(fl,CApp,2,0),IF(countryLink="FR",
VLOOKUP(fl,FRpp,2,0))))))))=TRUE,"",IF(countryLink="United States",
VLOOKUP(fl,USpp,2,0),IF(countryLink="United Kingdom",
VLOOKUP(fl,UKpp,2,0),IF(countryLink="Germany",VLOOKUP(fl,DEpp,2,0),
IF(countryLink="Australia",VLOOKUP(fl,AUpp,2,0),IF(countryLink="Canada",
VLOOKUP(fl,CApp,2,0),IF(countryLink="France",VLOOKUP(fl,FRpp,2,0))))))))

[/pre]
Note:

1. "countryLink", "XXpp" (where XX stands for a country name), "fl" are all named ranges.


Thanks to everyone for your time in advance!


AJain

 

[xld]

Create another named range called countries with this data


Australia AU

Canada CA

France FR

Germany DE

United Kingdom UK

United States US


and use


=IF(ISERROR(VLOOKUP(fl,INDIRECT(VLOOKUP(countryLink,countries,2,FALSE)&"pp"),2,FALSE)),"",

VLOOKUP(fl,INDIRECT(VLOOKUP(countryLink,countries,2,FALSE)&"pp"),2,FALSE))

 

[Ajain]

Thanks a ton xld! It works like a charm. I never really thought about approaching it this way. I learnt something new today and thank you again for teaching it to me!