lookup or macro to find and then get value...

[hubris_one]

for instance i want a formula or macro to lookup a value in say B5 and then find the match on another sheet and give me the value 5 cells over and 1 cell down (offset?).

 

[Jay Petrulis]

Hi,

Not quite sure I follow completely, but if the lookup range is col A in Sheet2 the following worked for me:

=OFFSET(INDIRECT("Sheet2!A"&MATCH(B5,Sheet2!A:A,0)),5,1)

This doesn't seem very efficient to me, so if I can find a better solution, I'll repost.

Bye,
Jay

EDIT: Should be

=OFFSET(INDIRECT("Sheet2!A"&MATCH(B5,Sheet2!A:A,0)),1,5)
This message was edited by Jay Petrulis on 2002-04-25 15:50

 

[EdE]

This will work too...

=index(sheet2!a2:z100,match(b5,sheet2!a2:a100,false),5)

I actually worked on this for something today!!
Hope it works for you!!

 

[Mark W.]

On 2002-04-25 15:48, EdE wrote:
This will work too...

=index(sheet2!a2:z100,match(b5,sheet2!a2:a100,false),5)

I actually worked on this for something today!!
Hope it works for you!!

Make that...

=INDEX(sheet2!A2:Z100,MATCH(B5,sheet2!A2:A100,0)<font color=red>+1,6</font>)

Note that INDEX has 1 advantage of OFFSET -- it's not volatile. See http://www.decisionmodels.com/calcsecretsi.htm.
This message was edited by Mark W. on 2002-04-25 16:13