VLOOKUP Formula from one cell to 2nd cell

[Guest]

Hi can someone help
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 (f8) 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 the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.

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

 

[Guest]

I'd highly recommend you use a separate space (other sheet, if you want to,
or just a separate place on the same sheet, and just white color the text so
it's not visible). List down one column all your product names. List down
the adjoining column the values. Name that range whatever you like (let's
say "ProdLookup" for this example). Let's say your drop down is in cell A1.
Formula to use next to would be:

vlookup(a1,prodlookup,2,false).

English translation is that it will find a1 in the first COLUMN, then shift
over to the 2nd column of that range, and return the result. It will ONLY
find exact matches, because you say false, as opposed to not stating false or
putting in true, which makes the formula find the closest thing to it, and
then take the next value (in case of not an exact match...basically, you
pretty much always want to use false parameter).

Just FYI, an HLookup, would look in a row and then down, as opposed to down,
and across.

That should do it for you. If you wanted to go to your validation of A1 and
use the same list of products, you could name just the first column Products,
and have that be the list value in your validation.

 

[Bob Phillips]

They need to be ordered. Try VLOOKUP

=VLOOKUP(F8;{"Fusion",15;"Openzone",35.7;"Switch",57;"Voip",78;"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 somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob i like your idea and it looks right, but its not accepting it, it says
there is an error on the formula. F8 is being highlighted... what do i do?

 

[Bob Phillips]

It could 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 somewhere in email address with gmail if mailing direct)

 

[Guest]

sorry bob i dont understand, what do u mean by NG? all i need is this formula
to work.
the only thing is stopin it from working is the 1st part =VLOOKUP(D6
have you actually checked this formula coz i think u have a mistake in it
somewhere.

Many thanks
Moh

 

[Bob Phillips]

You are right, I lost the first ; in my rewrite

=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 somewhere in email address with gmail if mailing direct)

 

[Guest]

hey bob your right it works,

many thanks

now one more question
i got a drop down list in cell d5 to d27 as you know,

what formula do i write in cell f5 to work out how much of each product i am
selling?

example: if i need to check how many Fusions i have sold

 

[Bob Phillips]

I don't know what the data looks like, but it would be something like

=COUNTIIF(D,"Fusion")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)