| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vLookup with multiple lookup value in table array | Excel Worksheet Functions | |||
| Inverse Table Lookup | Excel Worksheet Functions | |||
| formatting a cell the same as the source cell from a lookup table | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
