Look-up between two workbooks

[Zack Piotrowski]

Hello

I am faced with a dilemma. I am working on an asset allocation analysis
tool for clients regarding asset weightings with their holdings. I have
everything set up nice, but want to automate the process. I would like to
know if the following is possible, and if so, how??

I want to type in a code (the mutual fund code) in one cell, and
automatically have it come up with the name, and different asset weightings
in the adjacent cells. The source data is located in another workbook, all
on one page. The cells are side by side, and have all the data in the
correct order.

Now, does anyone know of an add-in, or a generic formula that can be
applied. Note that the data runs horizontally, while each selection runs
after each other vertically.

example

aim1256 aim money market xxx. 23% 34% 45%
aim1257 ditto

the first code, "aim1256" and the one below will be types in on the
statement, while the rest of the data is hoped to appear.

Thanks for any help provided.

 

[wiseman]

Zack,
Say your data was in Book2, sheet1 in columns A to G
and in Book3 you wanted to type in 'aim1256' in A4 to have the data come up
in B4, C4 etc
then the formula in B4 would be
=VLOOKUP($A4,[Book2]Sheet1!$A:$G,2,FALSE)
and in C4
=VLOOKUP($A4,[Book2]Sheet1!$A:$G,3,FALSE)

you can copy this formula to D4 and change the 3 to 4
What the formula is saying is look at A4 then compare with Book2, sheet1
find a match in column A and give me the contents of the cell in the 3rd
column (where A is column 1). The false means the data in the lookup table
does not have to be sorted, but it will only pick up an exact match. Watch
out for looking up numbers which could be treated as numbers in one place
and text in the other, or text with a space at the end. Both these will
look to the human the same but the computer will not recognise that they are
the same.

The easiest way to build formulae including vlookups is to use the formula
wizard (the tool 'fx' in your toolbar) you are then prompted to fill in each
section. When you want to pick up something from another sheet or workbook
just click in the appropriate box in the formula wizard (in vlookup usually
the table_array) click on window, choose the book required, or click on the
sheet tab to find the book required, or both. Then select the range, or the
columns by dragging the mouse.

If you need any more help with vlookup, ie avoiding the errors when there is
no data etc, do post back.
Barbara