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.


.



.