|
VLOOKUP Formula from one cell to 2nd cell
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 |
VLOOKUP Formula from one cell to 2nd cell
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 |
VLOOKUP Formula from one cell to 2nd cell
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) "Moh" wrote in message ... 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","C NI","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 |
VLOOKUP Formula from one cell to 2nd cell
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? -- please can you help... its urgent "Bob Phillips" wrote: 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) "Moh" wrote in message ... 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","C NI","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 |
VLOOKUP Formula from one cell to 2nd cell
It could be NG wrap-around
=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8; "Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 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) "Moh" wrote in message ... 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? -- please can you help... its urgent "Bob Phillips" wrote: 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) "Moh" wrote in message ... 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","C NI","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 |
VLOOKUP Formula from one cell to 2nd cell
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 -- please can you help... its urgent "Bob Phillips" wrote: It could be NG wrap-around =VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8; "Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 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) "Moh" wrote in message ... 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? -- please can you help... its urgent "Bob Phillips" wrote: 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) "Moh" wrote in message ... 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","C NI","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 |
VLOOKUP Formula from one cell to 2nd cell
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;"F eatureline 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) "Moh" wrote in message ... 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 -- please can you help... its urgent "Bob Phillips" wrote: It could be NG wrap-around =VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8; "Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 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) "Moh" wrote in message ... 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? -- please can you help... its urgent "Bob Phillips" wrote: 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) "Moh" wrote in message ... 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","C NI","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 |
VLOOKUP Formula from one cell to 2nd cell
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 -- please can you help... its urgent "Bob Phillips" wrote: 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;"F eatureline 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) "Moh" wrote in message ... 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 -- please can you help... its urgent "Bob Phillips" wrote: It could be NG wrap-around =VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8; "Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 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) "Moh" wrote in message ... 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? -- please can you help... its urgent "Bob Phillips" wrote: 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) "Moh" wrote in message ... 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","C NI","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 |
VLOOKUP Formula from one cell to 2nd cell
I don't know what the data looks like, but it would be something like
=COUNTIIF(D:D,"Fusion") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Moh" wrote in message ... 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 -- please can you help... its urgent "Bob Phillips" wrote: 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;"F eatureline 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) "Moh" wrote in message ... 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 -- please can you help... its urgent "Bob Phillips" wrote: It could be NG wrap-around =VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8; "Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 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) "Moh" wrote in message ... 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? -- please can you help... its urgent "Bob Phillips" wrote: 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) "Moh" wrote in message ... 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","C NI","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 |
| All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com