excel formulas?

Question

I need to be able to type in a part number and have it put price in the cell next to it. the part number is from another sheet in my work book.

Ok for example I have a time sheet with part numbers. So i want to be able to type in the part number ie. 1234 in column A then in column B i want it to print the price 10.44. This information is found on another sheet in excel. So first sheet is Payroll sheet and second sheet is Parts. I want to type in part number on payroll sheet and it get the information from Parts sheet.

Answer 1

ok

Answer 2

You need to use the VLOOKUP function. The VLOOKUP has four arguments. The arguments are what go inside the parenthesis. The arguments are separated by commas inside the parenthesis. I'm going to go through the arguments in order and how you might set them up.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

1. lookup_value - This is going to reference the column where you put your part numbers. So if you enter this formula in Column B for the prices and enter the part number in Column A, the first argument value would be the correlating part number cell in column A. So if you entered this formula in B2, you would enter A2 for the first argument.

2. table_array - this is going to be the reference table that Excel checks to get the price. This table should be on another worksheet most likely but it doesn't have to be. Sometimes it's just easiest to name the cell range and enter the named range for this argument or you could just select the cells. Most likely this should be Column A for the part numbers on another worksheet and Column B for the correlating prices. So you'd select both columns A and B.

3. col_index_num - This is the number of the column in the reference table (table_array) that you are going to draw your price value from. A = 1, B = 2, C = 3, D= 4 etc. The number cannot be a negative number since Excel doesn't allow a letter before A.

4. range_lookup - This tells Excel whether you want to find an exact part number match or if you want to find the closest match. If you set this argument to False, it will only return exact matches. For your project, I'd recommend setting the value to False. If it doesn't find an exact match it will give you the error #N/A. If you set this argument to True, it will look for an exact match first. If it doesn't find an exact match it will give you a match that is the closest value that is less than the value you specified for the lookup_value. If it can't find an exact value or a value less than the lookup_value it will give you the error #N/A.

The VLOOKUP function is extra handy because it allows for changes to be made easily to the main table containing the prices and the values to be adjusted accordingly.

Answer 3

Use Vlookup
Eg.....

Say your price list is in the range A1:B10 & in this range , part no column has to come before other/s. So if you have part no in column A and price in column B, that's fine. If it is not the case, you'll have to insert a column & set up the order.

Go to the cell B2 in the "Part No" Sheet where A2 is used to enter the part number

Type in B2
=VLOOKUP (A2,PRICE!$A$1:$B$10,2,0)

A2 = Part no =====> lookup value

PRICE!$A$1:$B$10 ==============> lookup table/price sheet

"2" ===========> value in the 2nd column

"False"=========> Exact match

If you need more info, add details or email

Answer 4

The best way would be to use the vlookup formula. This is a very easy to use and common formula. You should be able to find out how to use it by using the help function or looking on the internet. I would include an example, but you can't insert tables or pictures here.

Answer 5

You'll need to use the Lookup Function, referencing the price array in the other worksheet. Check out the help files on the Lookup Function.

Answer 6

That doesn't make much sense. You're going to have to be more specific... Although what I think you want is pretty easy and excel should do it for you.

Try double clicking on the = sign, which should bring up the function box. Click on the function you want, then click on the corresponding cells, regardless of what sheet they are on.

Answer 7

click on the cell where you want to put the part number.
type =
then click on the cell in the other worksheet
press enter

hope this helps

Answer 8

Check out my example here
http://www.freewebs.com/davesexcel/VLOOKUP%20Example2.xls