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!

On the third line,

"Standard",100";

should be

"Standard",100;

Julie P. wrote:

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!

"garfield-n-odie" wrote in message
...
On the third line,

"Standard",100";

should be

"Standard",100;



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

Don't feel stupid. I had a long drawn-out reply here that I happened to
never send 'cause I didn't think it was right. And it wasn't. Nice catch,
garfield-n-odie!
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Julie P." wrote in message
...

"garfield-n-odie" wrote in
message
...
On the third line,

"Standard",100";

should be

"Standard",100;



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

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

"Julie P." wrote in message
...
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!

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

In article ,
says...
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!