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.
--
Boris


"Moh" wrote:

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","M aintanance","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})


--
please can you help... its urgent