LOOKUP function syntax across a table?
 

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?

LOOKUP function syntax across a table?
 

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.

--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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?

LOOKUP function syntax across a table?
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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?

LOOKUP function syntax across a table?
 

Try this (still array entered):

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


--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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?

LOOKUP function syntax across a table?
 

That solved it. Thanks for your help!


"T. Valko" wrote:

Try this (still array entered):

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


--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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?

LOOKUP function syntax across a table?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
That solved it. Thanks for your help!


"T. Valko" wrote:

Try this (still array entered):

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


--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Marc" wrote in message
...
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?

LOOKUP function syntax across a table?
 

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

"Marc" wrote in message
...
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?