I am trying to have a worksheet locate a single data value based on two,
variable input values. The single data value is located at the intersection
of two variables in tables within the workbook. LOOKUP functions are only
good for one variable and column/row identifier. Since I have two variable
inputs, this won't work. Any ideas would be extremely helpful. Thanks.

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based on two,
variable input values. The single data value is located at the intersection
of two variables in tables within the workbook. LOOKUP functions are only
good for one variable and column/row identifier. Since I have two variable
inputs, this won't work. Any ideas would be extremely helpful. Thanks.

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran a test
I'm getting the old "#N/A" reading. I don't believe this is a formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears to be in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based on two,
variable input values. The single data value is located at the intersection
of two variables in tables within the workbook. LOOKUP functions are only
good for one variable and column/row identifier. Since I have two variable
inputs, this won't work. Any ideas would be extremely helpful. Thanks.

Hi,

No that won't work. There's no problem using the named range for the table
but your can't use that same named range for both of the match values.

What in effect that does is say MATCH(B3,a1:E20) so your not referring to
the header rows or columns, your referring to the entire table. Say your MATL
named range is in E4 to H14 you would need to change your formula to this

=INDEX(MATL, MATCH(B3,E4:E14,0), MATCH(B7,E4:H4,0))

or because there's no problem with named ranges overlapping you could create
2 more named ranges within MATL that refer to the header column and row and
use the formula

=INDEX(MATL, MATCH(B3,HdrCol,0), MATCH(B7,HdrRow,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"jj023" wrote:

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran a test
I'm getting the old "#N/A" reading. I don't believe this is a formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears to be in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based on two,
variable input values. The single data value is located at the intersection
of two variables in tables within the workbook. LOOKUP functions are only
good for one variable and column/row identifier. Since I have two variable
inputs, this won't work. Any ideas would be extremely helpful. Thanks.

Here's a small sample file that demonstrates 2 formula methods with named
ranges.

jj023.xls 14kb

http://cjoint.com/?cqshQOczdT

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

No that won't work. There's no problem using the named range for the table
but your can't use that same named range for both of the match values.

What in effect that does is say MATCH(B3,a1:E20) so your not referring to
the header rows or columns, your referring to the entire table. Say your
MATL
named range is in E4 to H14 you would need to change your formula to this

=INDEX(MATL, MATCH(B3,E4:E14,0), MATCH(B7,E4:H4,0))

or because there's no problem with named ranges overlapping you could
create
2 more named ranges within MATL that refer to the header column and row
and
use the formula

=INDEX(MATL, MATCH(B3,HdrCol,0), MATCH(B7,HdrRow,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"jj023" wrote:

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran a
test
I'm getting the old "#N/A" reading. I don't believe this is a formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears to be
in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based on
two,
variable input values. The single data value is located at the
intersection
of two variables in tables within the workbook. LOOKUP functions are
only
good for one variable and column/row identifier. Since I have two
variable
inputs, this won't work. Any ideas would be extremely helpful.
Thanks.

Thank you both so much. I was not familiar with these functions and the
capabilities. i will study up on them as this will save me a ton of time
once I learn how to use them efficiently. Thanks again!

jj023

"T. Valko" wrote:

Here's a small sample file that demonstrates 2 formula methods with named
ranges.

jj023.xls 14kb

http://cjoint.com/?cqshQOczdT

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

No that won't work. There's no problem using the named range for the table
but your can't use that same named range for both of the match values.

What in effect that does is say MATCH(B3,a1:E20) so your not referring to
the header rows or columns, your referring to the entire table. Say your
MATL
named range is in E4 to H14 you would need to change your formula to this

=INDEX(MATL, MATCH(B3,E4:E14,0), MATCH(B7,E4:H4,0))

or because there's no problem with named ranges overlapping you could
create
2 more named ranges within MATL that refer to the header column and row
and
use the formula

=INDEX(MATL, MATCH(B3,HdrCol,0), MATCH(B7,HdrRow,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"jj023" wrote:

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran a
test
I'm getting the old "#N/A" reading. I don't believe this is a formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears to be
in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based on
two,
variable input values. The single data value is located at the
intersection
of two variables in tables within the workbook. LOOKUP functions are
only
good for one variable and column/row identifier. Since I have two
variable
inputs, this won't work. Any ideas would be extremely helpful.
Thanks.


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jj023" wrote in message
...
Thank you both so much. I was not familiar with these functions and the
capabilities. i will study up on them as this will save me a ton of time
once I learn how to use them efficiently. Thanks again!

jj023

"T. Valko" wrote:

Here's a small sample file that demonstrates 2 formula methods with named
ranges.

jj023.xls 14kb

http://cjoint.com/?cqshQOczdT

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

No that won't work. There's no problem using the named range for the
table
but your can't use that same named range for both of the match values.

What in effect that does is say MATCH(B3,a1:E20) so your not referring
to
the header rows or columns, your referring to the entire table. Say
your
MATL
named range is in E4 to H14 you would need to change your formula to
this

=INDEX(MATL, MATCH(B3,E4:E14,0), MATCH(B7,E4:H4,0))

or because there's no problem with named ranges overlapping you could
create
2 more named ranges within MATL that refer to the header column and row
and
use the formula

=INDEX(MATL, MATCH(B3,HdrCol,0), MATCH(B7,HdrRow,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran a
test
I'm getting the old "#N/A" reading. I don't believe this is a
formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears to
be
in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based
on
two,
variable input values. The single data value is located at the
intersection
of two variables in tables within the workbook. LOOKUP functions
are
only
good for one variable and column/row identifier. Since I have two
variable
inputs, this won't work. Any ideas would be extremely helpful.
Thanks.


.

Very nice Biff, I never thought of specifying the row/col index of the named
range to direct match to the correct row/col. I'll remember that for future
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jj023" wrote in message
...
Thank you both so much. I was not familiar with these functions and the
capabilities. i will study up on them as this will save me a ton of time
once I learn how to use them efficiently. Thanks again!

jj023

"T. Valko" wrote:

Here's a small sample file that demonstrates 2 formula methods with named
ranges.

jj023.xls 14kb

http://cjoint.com/?cqshQOczdT

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

No that won't work. There's no problem using the named range for the
table
but your can't use that same named range for both of the match values.

What in effect that does is say MATCH(B3,a1:E20) so your not referring
to
the header rows or columns, your referring to the entire table. Say
your
MATL
named range is in E4 to H14 you would need to change your formula to
this

=INDEX(MATL, MATCH(B3,E4:E14,0), MATCH(B7,E4:H4,0))

or because there's no problem with named ranges overlapping you could
create
2 more named ranges within MATL that refer to the header column and row
and
use the formula

=INDEX(MATL, MATCH(B3,HdrCol,0), MATCH(B7,HdrRow,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran a
test
I'm getting the old "#N/A" reading. I don't believe this is a
formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears to
be
in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value based
on
two,
variable input values. The single data value is located at the
intersection
of two variables in tables within the workbook. LOOKUP functions
are
only
good for one variable and column/row identifier. Since I have two
variable
inputs, this won't work. Any ideas would be extremely helpful.
Thanks.


.



.

That's one of the "disadvantages" of using named ranges. Now we have to use
the additional function INDEX to specify the headers. Or, we can give the
headers their own names as you noted in your other post.

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Very nice Biff, I never thought of specifying the row/col index of the
named
range to direct match to the correct row/col. I'll remember that for
future
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jj023" wrote in message
...
Thank you both so much. I was not familiar with these functions and
the
capabilities. i will study up on them as this will save me a ton of
time
once I learn how to use them efficiently. Thanks again!

jj023

"T. Valko" wrote:

Here's a small sample file that demonstrates 2 formula methods with
named
ranges.

jj023.xls 14kb

http://cjoint.com/?cqshQOczdT

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

No that won't work. There's no problem using the named range for the
table
but your can't use that same named range for both of the match
values.

What in effect that does is say MATCH(B3,a1:E20) so your not
referring
to
the header rows or columns, your referring to the entire table. Say
your
MATL
named range is in E4 to H14 you would need to change your formula to
this

=INDEX(MATL, MATCH(B3,E4:E14,0), MATCH(B7,E4:H4,0))

or because there's no problem with named ranges overlapping you
could
create
2 more named ranges within MATL that refer to the header column and
row
and
use the formula

=INDEX(MATL, MATCH(B3,HdrCol,0), MATCH(B7,HdrRow,0))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"jj023" wrote:

Hello Mike,

Thanks for the input. I believe it will work. However, when I ran
a
test
I'm getting the old "#N/A" reading. I don't believe this is a
formatting
problem. Here's the formula I used (MATL is the range):

=INDEX(MATL, MATCH(B3,MATL,0), MATCH(B7,MATL,0))

I've checked both input cells and the table and everything appears
to
be
in
order. It should be producing "-64%".

Any thoughts?

Thanks again,

jj023

"Mike H" wrote:

Hi,

A general formula for a table lookup would be this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where
A1:E20 is the entire table including row and column headers
F1 is the value to match in the column
G1 is the value to match in the row

The intersect of the 2 is returned
--
Mike

When competing hypotheses are otherwise equal, adopt the
hypothesis
that
introduces the fewest assumptions while still sufficiently
answering
the
question.


"jj023" wrote:

I am trying to have a worksheet locate a single data value
based
on
two,
variable input values. The single data value is located at the
intersection
of two variables in tables within the workbook. LOOKUP
functions
are
only
good for one variable and column/row identifier. Since I have
two
variable
inputs, this won't work. Any ideas would be extremely helpful.
Thanks.


.



.