How to return a value from another workbook
 

I got a workbook named "Monitor Book" in which I need to lookup for the
targetvalue of a reference "ITEM" in cell a1 from another Book named as
"DATABASE". I will be updating the DATABASE every month so the ITEM and the
related figures in the cell keeps moving to downward rows. But the target
value of the ITEM in the DATABASE sheet will be always existing in the cell
which is 2 columns right side and 3 rows down even if its address changes its
cell location in the column A when I update the DATABASE sheet. I do not know
how to use the vlookup formula as the target value is in the 3 raws down and
2 columns right side.
So if the target value to be returned in cell a1 of Monitor Book from
DATABASE workbook looking for the reference ITEM and its target value what is
the formula.
Can anyone explain it with an worked example?

How to return a value from another workbook
 

Try

MATCH() INDEX() combination
I assume that
1. lookup value is in Col A
2. Target value is in colC (2 cols right from ColA)
3. Target value is 3 rows down from the lookup value

=INDEX([Database.xls]Sheet1!C:C,MATCH("ITEM",[Database.xls]Sheet1!A:A,0)+3)

If this post helps click Yes
---------------
Jacob Skaria


"Narnimar" wrote:

I got a workbook named "Monitor Book" in which I need to lookup for the
targetvalue of a reference "ITEM" in cell a1 from another Book named as
"DATABASE". I will be updating the DATABASE every month so the ITEM and the
related figures in the cell keeps moving to downward rows. But the target
value of the ITEM in the DATABASE sheet will be always existing in the cell
which is 2 columns right side and 3 rows down even if its address changes its
cell location in the column A when I update the DATABASE sheet. I do not know
how to use the vlookup formula as the target value is in the 3 raws down and
2 columns right side.
So if the target value to be returned in cell a1 of Monitor Book from
DATABASE workbook looking for the reference ITEM and its target value what is
the formula.
Can anyone explain it with an worked example?