Adding variables

[cj21]

Hi

I have 2 tabs.

The first tab includes the following data

Product Code
NO*929125923
ESB35107911
ZA*894038981
NO*929125925
NL201442890000
GB00424948
GB00884749


The second tab has more detail

Product Code Total Assets1997 Total Assets1998
NO*929125923 234 25
ESB35107911 24
ZA*894038981 356 245
GB00884749 5356 23
GB004806293 356 25
GB02352403 36 2354

I need a formula in the first tab that searches for the product code and then puts in total assets. So my 1st tab would look like:


Product Code Total Assets1997 Total Assets1998
NO*929125923 234 25
ESB35107911 24
ZA*894038981 356 245
NO*929125925 0 0
NL201442890000 0 0
GB00424948 0 0
GB00884749 0 0


In reality there are lots of product codes!

Thanks for your help!

[tigeravatar]

cj21,

What you're looking for is the VLookup() formula. Attached is an example workbook based on the sample data you provided. In Sheet2 is the more detail. In Sheet1 are the product codes with the vlookup() formulas to pull information from Sheet2. Assuming Sheet1 and Sheet2 have the same headers like in your sample data, then in Sheet1 cell B2, you can use the following formula:
=IF(ISNUMBER(MATCH($A2,Sheet2!$A:$A,0)),VLOOKUP($A2,Sheet2!$A:$C,MATCH(B$1,Sheet2!$1:$1,0),FALSE),0)

Then just copy over and down

Hope that helps,
~tigeravatar