complex index function not working

[mmcap]

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

 

[T. Valko]

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)

 

[mmcap]

It is giving an incorrect result. It always returns the name in the first
column of names. What I want is to have the name of the techs at the top of
the page, just to the right of the tools that are inventory. If I assign a
tool to one of the techs I would put the tool # that was assigned to the tech
in the column under his name and place it at the intersection of the row
where that tool# is listed in the inventory to the left.
INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME))) is just the part that isn’t
working of a larger lookup formula.
=IF(B2="","",IFERROR(INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME))),"NOT FOUND"))
Is the formula that works perfect except when I tried to adapt it to index at
the top instead of down the side. The box and bin numbers are already down
the side so I can’t put the names down the side. B2 on sheet 1is the input
cell for the tool location search. So when there is a tool # in B2, and that
tool is assigned to a tech I want it to display the techs name instead of the
box and bin location. I hope this information helps.

 

[T. Valko]

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

Tell me what the specific ranges are for:

NAME
TECHS

 

[mmcap]

NAME is H1,I1,J1,K1
TECHS is H,I,J,K2 through H,I,J,K1200

 

[T. Valko]

The formula I suggest returns the correct result:

Array entered** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[mmcap]

It works perfectly. Thank you so much!

The formula I suggest returns the correct result:

Array entered** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

You're welcome. Thanks for the feedback!