# FORMULA FOR EXPERT, PLS MAKE THIS WORK

G

#### Guest

Hi can someone help
IM USING EXCEL XP NOT 2003 and I have created a spreadsheet to work out how
much money people will make from each product.

the 20 products are all in one cell (D6) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP then
in the cell next to it, it should bring up 7.8, the price of it.

this is what im using but it highlights =VLOOKUP(D6 and says there is an
error with the formula.

=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",57;"Voip",7.8;"Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline 3
Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;"Mobile Low
Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;"VAS -
StaticIP",7.53};2;FALSE)

B

#### Bob Phillips

It might be NG wrap-around

=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",57;"Voip",7.8;
"Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;
"Featureline 3 Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;
"Mobile Low Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;
"VAS - StaticIP",7.53};2;FALSE)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Moh said:
Hi can someone help
IM USING EXCEL XP NOT 2003 and I have created a spreadsheet to work out how
much money people will make from each product.

the 20 products are all in one cell (D6) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP then
in the cell next to it, it should bring up 7.8, the price of it.

this is what im using but it highlights =VLOOKUP(D6 and says there is an
error with the formula.
=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",57;"Voip",7.8;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline 3

B

#### Bob Phillips

It's continental Excel Blue, they use ; for the separator.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Blue Aardvark said:
What about if you put .... ,2,FALSE) instead of ;'s in the formula
=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",57;"Voip",7.8;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline 3

J

#### Jefgorbach

It might be too late to implement, but with 20+ item list you might
consider either doing the vlookup to a seperate sheet or replacing with
a custom function using a select...case as either would be easier to
maintain/update price changes in one place than each individual cell.