....return the column number where a value is found in an array.

For example:
I have an array, A:AZ where 10/1/2007 is in, say, the fifth column. I want
a formula to return 5.

I have a vlookup that is based on a query output of data from 1/1/2007 to
the current month. One of the summary sheets I am working on only shows the
most recent 13 months. Last month 10/1/2007 was the second column. This
month it would be the first. Next month it would not be there at all and so
on. I want to be able to feed that column position to the column_index_num
in the vlookup.

There are other places in the report where I use all the data returned, so I
cannot limit the data to the most recent 13 months. I currently have two
queries, one for the 13 months and one for all the data. They both contain
the same 13 months of data so I would like to eliminate the redundant piece.

Suggestions? If you have done this and have a better way of doing, feel
free to share!

Thanks!
PJ

The MATCH function will return the relative position of the first
matching cell.

You can use INDIRECT to form a cell or range reference from a string
which is built up to represent the reference.

If XL Help does not describe these well enough for you, then post back
with some more details of how your data is laid out, and what you want
to accomplish.

Hope this helps.

Pete

On Oct 21, 6:43*pm, PJFry wrote:
...return the column number where a value is found in an array.

For example:
I have an array, A:AZ where 10/1/2007 is in, say, the fifth column. *I want
a formula to return 5.

I have a vlookup that is based on a query output of data from 1/1/2007 to
the current month. *One of the summary sheets I am working on only shows the
most recent 13 months. *Last month 10/1/2007 was the second column. *This
month it would be the first. *Next month it would not be there at all and so
on. *I want to be able to feed that column position to the column_index_num
in the vlookup. *

There are other places in the report where I use all the data returned, so I
cannot limit the data to the most recent 13 months. *I currently have two
queries, one for the 13 months and one for all the data. *They both contain
the same 13 months of data so I would like to eliminate the redundant piece. *

Suggestions? *If you have done this and have a better way of doing, feel
free to share!

Thanks!
PJ

MATCH did the trick!

Thanks!

"Pete_UK" wrote:

The MATCH function will return the relative position of the first
matching cell.

You can use INDIRECT to form a cell or range reference from a string
which is built up to represent the reference.

If XL Help does not describe these well enough for you, then post back
with some more details of how your data is laid out, and what you want
to accomplish.

Hope this helps.

Pete

On Oct 21, 6:43 pm, PJFry wrote:
...return the column number where a value is found in an array.

For example:
I have an array, A:AZ where 10/1/2007 is in, say, the fifth column. I want
a formula to return 5.

I have a vlookup that is based on a query output of data from 1/1/2007 to
the current month. One of the summary sheets I am working on only shows the
most recent 13 months. Last month 10/1/2007 was the second column. This
month it would be the first. Next month it would not be there at all and so
on. I want to be able to feed that column position to the column_index_num
in the vlookup.

There are other places in the report where I use all the data returned, so I
cannot limit the data to the most recent 13 months. I currently have two
queries, one for the 13 months and one for all the data. They both contain
the same 13 months of data so I would like to eliminate the redundant piece.

Suggestions? If you have done this and have a better way of doing, feel
free to share!

Thanks!
PJ

You're welcome, PJ - thanks for feeding back.

Pete

On Oct 21, 7:17*pm, PJFry wrote:
MATCH did the trick! *

Thanks!