Find a single value from tables by selecting two variable inputs?

[jj023]

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.

 

[Mike H]

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]

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]

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.

 

[T. Valko]

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

jj023.xls 14kb

http://cjoint.com/?cqshQOczdT

 

[jj023]

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]

You're welcome. Thanks for the feedback!

 

[Mike H]

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]

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.