wrapping offset around a lookup

[APV]

Hi there,

I am trying to do something that I sure is fairly easy, but my feeble
brain is not helping much at the moment.

I would like to combine OFFSET with one of the lookup functions so
that I can return a value that are some known X,Y offset of my target,
sometimes in the same column or row. (I should note that my range is
not a 'list' by an Excel standards, but rather a set of data with a
specific locational pattern)

For example, I am looking for 'Joe' in some range, and I know that in
that range, Joe's age is always two rows above wherever I find his
name (in the same column) and his address is always 3 columns to the
right, and two rows down. etc etc (obv my range is not a true list)
--- I am sure I am blind to some easy fix as to combining OFFSET with
the appropriate lookup function. I then wanna do the same to find
'Joanna' in the same range, as her age and address are the same
offsets from wherever she might be found.

An Excel guru has gotten me to how return the cell address:

=ADDRESS(ROW(Data)+MATCH(Target,Data,0)-1,COLUMN(Data))

but I cannot pass the output to OFFSET for some reason.

Any help would be appreciated. Thanks in advance.

 

[T. Valko]

It would have been better had you posted a"chunk" of data.

From what I gather:

If A5 = Joe
His age is always 2 rows above so that means his age is in A3.
His address is always 3 columns to the right and two rows down
so that means his address is in D7

To find Joe's age:

A20 = Joe

=OFFSET(A1,MATCH(A20,A1:A10,0)-3,)

To find Joe's address:

=OFFSET(A1,MATCH(A20,A1:A10,0)+1,3)

Biff

 

[APV]

Thanks Biff. That works very well.

One thing though, if my range is anything but one column or row ( say
11 columns by 500 rows) it returns a #N/A error. Any ideas on how to
address that? Thanks again.

 

[T. Valko]

What do you mean? Do you mean that "Joe" might be in any column?

If that's the case I'd seriously consider a redesign of you data layout!

Biff

 

[APV]

Hi Biff,

No Joe will always be in the first column, but data relating to Joe
might be in the same column or the next few columns and rows over.
Does that make sense?

As far as data layout is concerned, I am with you (we mix pure data
with client exhibitry) BUT it is unlikely that I could convince
everyone in my company to do so.

Any additional thoughts? Like I said your method is great, except it
doesn't work with my requisit range.

Thanks again,
APV

 

[T. Valko]

There has to be some pattern or key in order to find the data. For eaxmple,
a typical lookup table is based on intersections. The data is found at the
intersection of criteria1 (vertical axis) and criteria2 (horizontal axis).
Of course, it can get more complicated but it still boils down to some
pattern or key to look for.

Is it possible for me to see what your layout actually looks like?

Biff

 

[APV]

I would be happy and grateful to show you.

I tried emailing but your email bounced.

???

 

[T. Valko]

Try this one:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff