I can't make heads or tails out of the posted table!

=INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME)))

What's not working? Incorrect result? Error?

The result of MAX(...) has to be a number *relative* to the positions of the
indexed array. If NAME is a range of 4 cells then MAX(...) must return a
number from 1 to 4. Maybe you just need to do this.

Array entered:

=INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME))-MIN(COLUMN(NAME))+1)

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
Let me start out by saying that the following formula works quite well
{=INDEX(box_num,MAX((list=B2)*ROW(box_num))-1)} but when I tried to rework
it
like this {=INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME)))} and use it for
indexing a header at the top I cannot get it to return the names in the
header.
The following is an example of the sheet I am trying to get working. B2
in
the formulas above refer to B2 on sheet 1 of the workbook in which the
lookup
formulas will be. Below is sheet 2 of the workbook (the lookup tables).
The
4 cells with the names in them have been named (NAME). The cells under the
names are 4 wide and 1200 deep, this group of cells have been named
(TECHS)
for "techs tools". The T# under each name stands for a tool number which
came out of its normal storage box (numbers to the left) and has been
assigned to a tech to keep in his work area instead.
Am I just having a brain cramp and overlooking something simple? I just
want to get the index formula above to work the same way horizontally and
vertically. If anyone would like to e-mail me so I could send them a copy
of
the workbook instead of just a small section I can do that also. The
names
are supposed to be in the same row at the top with the (TOOL#) cells. it
didn't paste very well, sorry.

TOOL# TOOL# TOOL# TOOL# TOOL# BRANDON ED GARY NORM
BIN#10 BOX#1 T98T-56-789 T2 T3 T4 T5 T3
BIN#10 BOX#1 T6 T7
BIN#10 BOX#2 T98T-57-789 T8 t9 t10 t11 T11
BIN#10 BOX#2 T12
BIN#20 BOX#3 T98T-58-789 T13 T14 T15 T16
BIN#20 BOX#3 T17 T18 T19 T17
BIN#30 BOX#4 T98T-59-789 T20 T21 T22 T23
BIN#30 BOX#4 T24 T25 T25