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.

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" wrote in message
oups.com...
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.

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.



On Feb 20, 2:28 pm, "T. Valko" wrote:
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" wrote in message

oups.com...

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.

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" wrote in message
ups.com...
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.



On Feb 20, 2:28 pm, "T. Valko" wrote:
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" wrote in message

oups.com...

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.

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

On Feb 21, 10:20 am, "T. Valko" wrote:
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" wrote in message

ups.com...

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.

On Feb 20, 2:28 pm, "T. Valko" wrote:
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" wrote in message

groups.com...

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.

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" wrote in message
ups.com...
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

On Feb 21, 10:20 am, "T. Valko" wrote:
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" wrote in message

ups.com...

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.

On Feb 20, 2:28 pm, "T. Valko" wrote:
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" wrote in message

groups.com...

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.

I would be happy and grateful to show you.

I tried emailing but your email bounced.

???

On Feb 21, 11:29 pm, "T. Valko" wrote:
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" wrote in message

ups.com...

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

On Feb 21, 10:20 am, "T. Valko" wrote:
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" wrote in message

roups.com...

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.

On Feb 20, 2:28 pm, "T. Valko" wrote:
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" wrote in message

groups.com...

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.

Try this one:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"APV" wrote in message
ups.com...
I would be happy and grateful to show you.

I tried emailing but your email bounced.

???

On Feb 21, 11:29 pm, "T. Valko" wrote:
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" wrote in message

ups.com...

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

On Feb 21, 10:20 am, "T. Valko" wrote:
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" wrote in message

roups.com...

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.

On Feb 20, 2:28 pm, "T. Valko" wrote:
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" wrote in message

groups.com...

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.