My formula does not work!

J

Julie P.

Excel claims there is an error in my formula below, but I cannot find it:

=VLOOKUP(A1,{"Ground",100;"Surface",100;"Next Day Noon",100;"Next Day 10:30
AM",100;"Next Day 3 PM",100;"2nd Day",100;"Next Day Air",100;"2nd Day
Air",100;"Standard",100";"Priority Mail",0;"First-Class Mail",0;"Media
Mail",0;"Parcel Post",0;"Bound Printed Matter",0;"Express
Mail",100;"EMS",100;"Global Express Guaranteed",100;"Airmail Letter
Post",0;"Airmail Parcel Post",0;"Economy Letter Post",0;"Economy Parcel
Post",0;"Global Priority Mail",0;"Airmail Letter Post-Registered
Mail-Canada",100;"Economy Letter Post-Registered Mail-Canada",100;"Airmail
Letter Post-Registered Mail",43.93;"Economy Letter Post-Registered
Mail",43.93},2,FALSE)

Does anyone have any ideas??

This formula below DOES work though:
=VLOOKUP(A1,{"DHL-Domestic",0.19;"FedEx-Domestic",0.19;"UPS-Domestic",0.19;"DHL-International",0.30;"FedEx-International",0.30;"UPS-International",0.30;"USPS-Domestic-Traceable",0.48;"USPS-Domestic-Non-Traceable",0.68;"USPS-International",0.98},2,FALSE)

And the only thing different from the one above is that I just changed some
values!

Thanks!
 
J

Julie P.

garfield-n-odie said:
On the third line,

"Standard",100";

should be

"Standard",100;
Click to expand...


Garfield, you're a lifesaver! Thanks. I feel really stupid asking all these
questions tonight and for not proofreading better, but I have learned so
much!

Julie
 
B

Biff

That is one nasty formula!

How much of a PITA is it going to be when the rates change and you have to
modify that formula?

In your other post(s) someone suggested using a lookup table. If you did the
formula could be as simple as:

=VLOOKUP(A1,A3:B15,2,0)

Biff
 
T

Tushar Mehta

Just because XL might support such formulas is no reason to use them.
You couldn't figure out the problem with it and you wrote it. Imagine
somone else having to maintain these in the future!

Put the information you have in a table and use VLOOKUP with that
table. The result will be easier to understand, debug, and maintain!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top