hi all,
trying to do something for someone at work. I have got two spreadsheets...

Spreadsheet 1: has a pivot table looking at the products made and for whom in May (has both product code and details)

Spreadsheet 2: has a list of product codes and a weighting value associated with each.

I want Excel to look at spreadsheet 1 (column A) and look at the product code and pull the corresponding weighting value in spreadsheet 2 across and put it in column J of spreadsheet 1 for me.

I could simply just input it manually but there are over 45 products and it'll need to be done for the past 3-4 months so would take forever. Plus, not all the products are made each month so there is the risk of inputting the wrong weighting value.


Any suggestions on how to do this quickly and efficiently?

[Edited on 14/6/10 by mads]

What you want is Vlookup. It looks up values in a list

Example: http://www.automateexcel.com/2004/08/15/excel_formula_vlookup_function_example/

It sound like a vlookup to me, although I tend to Index and match as it negates the need for things to be sorted - although I understand that the latest version of Excel doesn't required columns to be sorted...

cheers guys. have updated the original post which makes more sense in what i am trying to achieve.

what version of excel are you using?

If it can be saved on 2003 or earlier format, email it to me and i'll do it for you.

running 2007 Mike.

well a vlookup would be....


=vlookup(value to look up so column a, highlight the columns of the table to look up the value, the column number you want to display, false).

By column number you want to display, if you had columns A,B,C,D,E,F and 'A' contains the value that is what you want to match and F contains the value you want to display you'd enter 6 as 'F' is the sixth value.

False means the computer doesn't find the nearest value - it can if you want find the closest match and report that. I've never used the feature so always specify false.

The problem you may have if referencing something from within a pivot table. I seem to recall trying it a few weeks ago, my 'simple' solution was to highlight the pivot table, copy and right click, past special, select values. then do the lookup against the new data you've just pasted.

Could you be after as simple as this?

2 sheets, 1 called 'sheet1', other 'sheet2'

in a cell sheet 1 type =Sheet2!A1

this will display the value of A1 of sheet2 in the cell of sheet 1 in which you type the above


[Edited on 14/6/10 by loggyboy]

OK, ran the VLOOKUp function and looks like what I needed. Problem is that in some instances it gives the weighting as expected and in some cells it gives the product description, which is in the column next to the product code. Weird!

I have since moved the workbook 2 into workbook 1 as another sheet

the error I get is #REF! which looking on MS website is:

quote:

---

Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:

- Less than 1, VLOOKUP returns the #VALUE! error value.
- Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

---

daft question - are all your numbers, numbers?

Excel can hold numbers or it can hold text that is a number,

eg, 123 is a number,
"123" is text

You can't do a vlookup using "123" to find 123.

My trick is to add a column at the start of every table and do
=text(field with number,0)
and then next to the column to be looked up do the same, eg,
=text(field with value to lookup,0).

This means excel is using the same format for the lookup values. If you're doing a lot of lookups you'll need to copy and paste values the columns so excel doesn't have to keep calculating them (on 80k row spreadsheets vlookups the copy and paste saves a lot of time).

oh, once you've got the '=text' values - resort your spreadsheet tab with the reference values as some may now be in a different order. Any out of order won't be found on the vlookup.

quote:

---

Originally posted by MikeR
daft question - are all your numbers, numbers?

---