The formula below may be longer than others, but it has the advantage
that it uses only a single range reference for the lookup table,
including row/column lookup values. If you name the lookup table
(including row headers on the left and column headers on the top),
"Tab", you can use

=OFFSET(Tab,MATCH(A1,OFFSET(Tab,0,0,ROWS(Tab),1),0 )-1,MATCH(B1,OFFSET(Tab,0,0,1,COLUMNS(Tab)),0)-1,1,1)

where A1 is the value to look up in the left-most column of Tab and B1
is the value to look up in the top row of Tab. So if you have data
like the following named Tab,

ColVal1 ColVal2 ColVal3
RowVal1
RowVal2
RowVal3 .... data .....
RowVal4


and A1 contains RowVal3 and B1 contains ColVal2, the formula will
return the value at the intersection of RowVal3 and ColVal2.

If a value isn't found, the result is #N/A.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Mon, 5 Oct 2009 04:34:01 -0700, dp99
wrote:

I know how to use HLOOKUPs, VLOOKUPs, but want to extract data from a table
based upon a specified value for both the x and y axis, ie I know that the
values appear in the header row and header column, but i don't know exactly
where these intersect (which you need for H & V lookups)