LOOKUP function syntax across a table?

[Marc]

I am trying to structure a LOOKUP formula which looks across a table of
values, and if it finds a particular value, it reports the ROW NAME's value.

For example, if I have a 10-by-10 matrix of numbers, with ROW NAMEs in
Column A, followed by a matrix of numbers within the table B2:K11:

Employee 1 2 3 4 5 6 7 8
9 10
E1 1.1 3.1 5.1 7.1 9.1 11.4 13.4 15.4 17.1 19.0
E2 1.3 3.4 5.4 7.4 9.4 11.1 13.1 15.1 17.3 19.8
E3 1.4 3.3 5.3 7.3 9.3 11.3 13.3 15.3 17.4 19.9
E4 1.2 3.6 5.6 7.6 9.6 11.6 13.6 15.6 17.6 19.7
E5 1.6 3.2 5.2 7.2 9.2 11.2 13.2 15.2 17.2 19.2
E6 1.7 3.7 5.7 7.7 9.7 11.7 13.7 15.7 17.7 19.6
E7 1.9 3.9 5.9 7.9 9.9 11.9 13.9 15.9 17.9 19.4
E8 1.0 3.8 5.8 7.8 9.8 11.8 13.8 15.0 17.8 19.3
E9 1.8 3.0 5.5 7.0 9.0 11.0 13.0 15.8 17.0 19.1
E10 1.5 3.5 5.0 7.5 9.5 11.5 13.5 15.5 17.5
19.5

I would like to come up with a function (INDEX, LOOKUP, MATCH, etc.) which
would allow me to search the table for a value (say "11.9") and return the
value from Column A (in this case "E7").

Any idea how I should construct the function call?

 

[T. Valko]

Try this array formula** :

A15 = lookup value = 11.9

=INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+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.

 

[Marc]

Biff,

That came really close. While the array works great if the value exists, if
the value does not exist it returns an error of #VALUE! from the formula. Is
there any tweak to the formula which could be used to either leave this blank
or insert a "Not Found" text string?

Thanks for your help!

 

[T. Valko]

Try this (still array entered):

=IF(COUNTIF(B2:K11,A15),INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1),"Not
Found")

 

[Marc]

That solved it. Thanks for your help!

Try this (still array entered):

=IF(COUNTIF(B2:K11,A15),INDEX(A2:A11,MAX((B2:K11=A15)*ROW(B2:K11))-MIN(ROW(B2:K11))+1),"Not
Found")

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Ashish Mathur]

Hi,

You can try this as well. I have assumed that the data below in range
C6:F9. In column enter nos. in ascending order starting from 1.

=INDEX($C$6:$G$9,SUMPRODUCT(($D$6:$F$9=E12)*(G6:G9)),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com