complex index function not working

  • Thread starter Thread starter mmcap
  • Start date Start date
M

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
 
I can't make heads or tails out of the posted table!
=INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME)))
Click to expand...

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)
 
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.
 
INDEX(NAME,,MAX((TECHS=B2)*COLUMN(NAME)))

Tell me what the specific ranges are for:

NAME
TECHS
 
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.
 
It works perfectly. Thank you so much!

T. Valko said:
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.
Click to expand...
 
Back
Top