lookup for a range around a cell in another workbook

  • Thread starter Thread starter Valeria
  • Start date Start date
V

Valeria

Dear experts,
I have 2 workbooks (say A and B). Workbook B contains a
range of data, and I would like to lookup 1 value (say
cell A1) from workbook A so that I do not only get that
cell (or the one next to it), but also a range of cells in
workbook B around that cell (I need to specify columns and
rows offset).
Basically, I am trying to make a targeted subset of
workbook B in workbook A without using VBA.

I am able to get the full address of the lookedup cell
thanks to an "address/match" formula, but Excel does not
accept this formula as input for the offset formula
reference field.

Could you please kindly advise?
Many thanks,
Valeria
 
Valeria said:
Dear experts,
I have 2 workbooks (say A and B). Workbook B contains a
range of data, and I would like to lookup 1 value (say
cell A1) from workbook A so that I do not only get that
cell (or the one next to it), but also a range of cells in
workbook B around that cell (I need to specify columns and
rows offset).
Basically, I am trying to make a targeted subset of
workbook B in workbook A without using VBA.

I am able to get the full address of the lookedup cell
thanks to an "address/match" formula, but Excel does not
accept this formula as input for the offset formula
reference field.

Could you please kindly advise?
Many thanks,
Valeria
Click to expand...

Don't try to get the "full address of the looked up cell" as that's not what
you need for use in other formulas, for example OFFSET. Instead, concentrate
on getting what you do need for OFFSET, which is row and column offsets from
a fixed cell.

For example, suppose you are using MATCH to find the value "r" in the range
$C$3:$C$10. Then $C$3 is your 'fixed' cell and the result of the MATCH is
one more than the row offset. So, to get the value in the cell immediately
to the left of the match, you would use:
=OFFSET($C$3,MATCH("r",$C$3:$C$10,0)-1,-1)
Other values in cells 'around' the match can be referenced simply by
adjusting the numbers which are -1 in the above formula. For example, the
value one column to the right and one row down from the match would be:
=OFFSET($C$3,MATCH("r",$C$3:$C$10,0),1)
 
-----Original Message-----


Don't try to get the "full address of the looked up cell" as that's not what
you need for use in other formulas, for example OFFSET. Instead, concentrate
on getting what you do need for OFFSET, which is row and column offsets from
a fixed cell.

For example, suppose you are using MATCH to find the value "r" in the range
$C$3:$C$10. Then $C$3 is your 'fixed' cell and the result of the MATCH is
one more than the row offset. So, to get the value in the cell immediately
to the left of the match, you would use:
=OFFSET($C$3,MATCH("r",$C$3:$C$10,0)-1,-1)
Other values in cells 'around' the match can be referenced simply by
adjusting the numbers which are -1 in the above formula. For example, the
value one column to the right and one row down from the match would be:
=OFFSET($C$3,MATCH("r",$C$3:$C$10,0),1)
Click to expand...


Hi Paul,
thanks.
My problem in this case is that I can't give any fixed
reference cell to the OFFSET function (as in your
case "$C$3"), as it's basically a looked up value coming
from another workbook.
To give a better ex:

Worksheet A (lookup) Worksheet B (initial database)

lookup value: 2 1 r y v d
looked up range: 2 a n s e v
p k
2 a n s e v 3 d a k m o
p k

the OFFSET has to refer to the "2" cell in Worksheet B,
and not A.

Is there any way I can perform this?

Many thanks,
Valeria
 
Back
Top