|
Lookup value in table
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. -- Bonita |
Lookup value in table
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 "Bonita" wrote in message ... 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. -- Bonita |
Lookup value in table
P.S.
In the formulas A10 holds the lookup value. Biff "Biff" wrote in message ... 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 "Bonita" wrote in message ... 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. -- Bonita |
Lookup value in table
Relative
The column location will be used to lookup another value. Thanks for the solution. -- Bonita "Biff" wrote: P.S. In the formulas A10 holds the lookup value. Biff "Biff" wrote in message ... 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 "Bonita" wrote in message ... 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. -- Bonita |
Lookup value in table
Thanks. It works great
-- Bonita "Bonita" wrote: Relative The column location will be used to lookup another value. Thanks for the solution. -- Bonita "Biff" wrote: P.S. In the formulas A10 holds the lookup value. Biff "Biff" wrote in message ... 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 "Bonita" wrote in message ... 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. -- Bonita |
Lookup value in table
You're welcome. Thanks for the feedback!
Biff "Bonita" wrote in message ... Thanks. It works great -- Bonita "Bonita" wrote: Relative The column location will be used to lookup another value. Thanks for the solution. -- Bonita "Biff" wrote: P.S. In the formulas A10 holds the lookup value. Biff "Biff" wrote in message ... 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 "Bonita" wrote in message ... 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. -- Bonita |
| All times are GMT +1. The time now is 01:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com