Producing a price list in excel

  Barnie Bears 20:43 10 Jan 05
Locked

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 05

Mailed you to the VoGtster

  consultik 23:47 10 Jan 05

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

  VoG II 17:47 11 Jan 05

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

  VoG II 20:29 16 Jan 05

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:

=VLOOKUP(D1,A1:B3,2,TRUE)

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 05

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:

=VLOOKUP(D1,Sheet1!A1:B3,2,TRUE)

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

What is Google Allo? What is Google Duo? Google Allo UK release date and features: Google Allo is…

1995-2015: How technology has changed the world in 20 years

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…