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)))

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

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 isnt
working of a larger lookup formula.
=IF(B2="","",IFERROR(INDEX(NAME,,MAX((TECHS=B2)*CO LUMN(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 cant 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" wrote:

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

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

Tell me what the specific ranges are for:

NAME
TECHS

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
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)*CO LUMN(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" wrote:

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

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


"T. Valko" wrote:

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

Tell me what the specific ranges are for:

NAME
TECHS

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
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)*CO LUMN(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" wrote:

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

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.

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
NAME is H1,I1,J1,K1
TECHS is H,I,J,K2 through H,I,J,K1200


"T. Valko" wrote:

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

Tell me what the specific ranges are for:

NAME
TECHS

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
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)*CO LUMN(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" wrote:

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

It works perfectly. Thank you so much!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
NAME is H1,I1,J1,K1
TECHS is H,I,J,K2 through H,I,J,K1200


"T. Valko" wrote:

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

Tell me what the specific ranges are for:

NAME
TECHS

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
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)*CO LUMN(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" wrote:

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
It works perfectly. Thank you so much!

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
NAME is H1,I1,J1,K1
TECHS is H,I,J,K2 through H,I,J,K1200


"T. Valko" wrote:

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

Tell me what the specific ranges are for:

NAME
TECHS

--
Biff
Microsoft Excel MVP


"mmcap" wrote in message
...
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)*CO LUMN(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" wrote:

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