HLOOKUP PROBLEM

[mmcap]

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 B25?
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

 

[Shane Devenshire]

Hi,

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

=INDEX(A2:A5,MAX((B25=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]

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

Hi,

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

=INDEX(A2:A5,MAX((B25=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

 

[Shane Devenshire]

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]

It works wonderfuly. Thank you very much

Norman