Lookup value in table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bonita - 23 Oct 2006 22:06 GMT

Need to determine column of the lookup value in a table.
Lookup value = 6/1/06

Table A B C
row 1 7/1/05 7/1/06 7/1/07
row 2 6/1/05 6/1/06 6/1/07
row 3 5/1/05 5/1/06 5/1/07

The answer should column 2.

Your help is greatly appreciated.

Signature

Bonita

Reply to this Message

Biff - 23 Oct 2006 22:24 GMT

Is the column number relative or absolute?

If your table is in the range A1:C3 this array formula entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER) will return the ABSOLUTE
column number:

=MAX((A1:C3=A10)*COLUMN(A1:C3)) Returns: 2

If your table is in the range D1:F3 this array formula entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER) will return the RELATIVE
column number:

=MAX((D1:F3=A10)*COLUMN(D1:F3)-COLUMN(D1)+1) Returns: 2

Biff

> Need to determine column of the lookup value in a table.
> Lookup value = 6/1/06
[quoted text clipped - 7 lines]
>
> Your help is greatly appreciated.

Reply to this Message

Biff - 23 Oct 2006 22:31 GMT

P.S.

In the formulas A10 holds the lookup value.

Biff

> Is the column number relative or absolute?
>
[quoted text clipped - 23 lines]
>>
>> Your help is greatly appreciated.

Reply to this Message

Bonita - 24 Oct 2006 14:51 GMT

Relative
The column location will be used to lookup another value.
Thanks for the solution.

Signature

Bonita

> P.S.
>
[quoted text clipped - 29 lines]
> >>
> >> Your help is greatly appreciated.

Reply to this Message

Bonita - 24 Oct 2006 16:01 GMT

Thanks. It works great

Signature

Bonita

> Relative
> The column location will be used to lookup another value.
[quoted text clipped - 33 lines]
> > >>
> > >> Your help is greatly appreciated.

Reply to this Message

Biff - 24 Oct 2006 19:16 GMT

You're welcome. Thanks for the feedback!

Biff

> Thanks. It works great
>
[quoted text clipped - 37 lines]
>> > >>
>> > >> Your help is greatly appreciated.

Reply to this Message