HLOOKUP PROBLEM

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

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 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
 
I gave that a try but I get #value in E2.

Shane Devenshire said:
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
 
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
 
Back
Top