Is there a way to get HLOOKUP to return the number of the box found in column
A when you do a search for one of the tool numbers in cells B2:D5?
A B C D
1 tool# tool# tool#
2 BOX #1 t1 t2 t3
3 BOX #2 t4 t5 t6
4 BOX #3 t7 t8 t9
5 BOX #4 t10 t11 t12

Hi,

Suppose the result of your search for a tool number is in cell E1 then in E2

=INDEX(A2:A5,MAX((B2:D5=E1)*ROW(A2:A5))-1)

In this example the t1 is in cell B2. This formula is array entered - Press
Shift+Ctrl+Enter to enter it.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

Is there a way to get HLOOKUP to return the number of the box found in column
A when you do a search for one of the tool numbers in cells B2:D5?
A B C D
1 tool# tool# tool#
2 BOX #1 t1 t2 t3
3 BOX #2 t4 t5 t6
4 BOX #3 t7 t8 t9
5 BOX #4 t10 t11 t12

I gave that a try but I get #value in E2.

"Shane Devenshire" wrote:

Hi,

Suppose the result of your search for a tool number is in cell E1 then in E2

=INDEX(A2:A5,MAX((B2:D5=E1)*ROW(A2:A5))-1)

In this example the t1 is in cell B2. This formula is array entered - Press
Shift+Ctrl+Enter to enter it.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

Is there a way to get HLOOKUP to return the number of the box found in column
A when you do a search for one of the tool numbers in cells B2:D5?
A B C D
1 tool# tool# tool#
2 BOX #1 t1 t2 t3
3 BOX #2 t4 t5 t6
4 BOX #3 t7 t8 t9
5 BOX #4 t10 t11 t12

Hi,

I tested it on your data and it works fine. Are you entering the formula as
an array - this means you do not type it in and press Enter, instead you
press Shift+Ctrl+Enter. Is that what you are doing?

cheers,
Shane Devenshire

"mmcap" wrote:

I gave that a try but I get #value in E2.

"Shane Devenshire" wrote:

Hi,

Suppose the result of your search for a tool number is in cell E1 then in E2

=INDEX(A2:A5,MAX((B2:D5=E1)*ROW(A2:A5))-1)

In this example the t1 is in cell B2. This formula is array entered - Press
Shift+Ctrl+Enter to enter it.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

Is there a way to get HLOOKUP to return the number of the box found in column
A when you do a search for one of the tool numbers in cells B2:D5?
A B C D
1 tool# tool# tool#
2 BOX #1 t1 t2 t3
3 BOX #2 t4 t5 t6
4 BOX #3 t7 t8 t9
5 BOX #4 t10 t11 t12

It works wonderfuly. Thank you very much

Norman

"Shane Devenshire" wrote:

Hi,

I tested it on your data and it works fine. Are you entering the formula as
an array - this means you do not type it in and press Enter, instead you
press Shift+Ctrl+Enter. Is that what you are doing?

cheers,
Shane Devenshire

"mmcap" wrote:

I gave that a try but I get #value in E2.

"Shane Devenshire" wrote:

Hi,

Suppose the result of your search for a tool number is in cell E1 then in E2

=INDEX(A2:A5,MAX((B2:D5=E1)*ROW(A2:A5))-1)

In this example the t1 is in cell B2. This formula is array entered - Press
Shift+Ctrl+Enter to enter it.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

Is there a way to get HLOOKUP to return the number of the box found in column
A when you do a search for one of the tool numbers in cells B2:D5?
A B C D
1 tool# tool# tool#
2 BOX #1 t1 t2 t3
3 BOX #2 t4 t5 t6
4 BOX #3 t7 t8 t9
5 BOX #4 t10 t11 t12