Producing a price list in excel

  Barnie Bears 20:43 10 Jan 2005

I am new to if functions etc. I have a price list in a workbook ie different columns showing qtys and the unit cost in the rows underneath.
I need to produce a form so we type in the qty required and the unit cost is calculated. I believe I can do this with if functions?? any help would be great thank you.

  spikeychris 21:43 10 Jan 2005

Mailed you to the VoGtster

  consultik 23:47 10 Jan 2005

I think you'd be better off using a Vlookup function - try typing vlookup into excel help.

  VoG II 17:47 11 Jan 2005

Do you mean input quantity and calculate *total* cost?

  VoG II 20:29 16 Jan 2005

Via e-mail.

Ie Columns with qtys and pricing, 200 500 1000 .20 .19 .18 I have tried the if function but it only works for two functions. We would then type in qty 600 so the result would be .18 if we typed in 250 the result would be .20 etc


You need to use VLOOKUP.

If your numbers are in A1:A3 and your prices are in B1:B3, and you type the quantity into D1 then the following formula (in any other cell) will return the correct unit cost:


D1 = the value you are looking up

A1:B3 = the range of your data table

2 = the column number to look up (B)

TRUE tells it to find the nearest match.

  VoG II 20:42 16 Jan 2005

Again via e-mail

Thank you so much - that is brilliant. Could I now ask you is it possible for me to have the price list on one sheet and the enquiry form on another so the look up looks at another sheet?


Please use the Add a response button at the bottom of the page. The yellow envelope is for private messages.

Yes, as before but your data are on Sheet1 and you type a quantity into D1 on Sheet2. Then in a cell in Sheet2 use the formula:


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy S9 review

Explore Milton Glaser's iconic poster designs from the 1960s to present, including Bob Dylan, I…

What to expect at Apple's 27 March education event

Idées cadeaux pour geeks et tech addicts