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)

For info on a two-way lookup see
http://www.contextures.com/xlFunctio...ml#IndexMatch2
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"dp99" wrote in message
...
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)

Try somthing like the below...

1st MAtch to get the row
2nd match to get the column

=INDEX(array,MATCH(value,A:A,0),MATCH(value,A1:J1, 0))

If this post helps click Yes
---------------
Jacob Skaria


"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)

Another way is to use VLOOKUP itself. To find the column number use MATCH()

=VLOOKUP(value,array,MATCH(value,A1:J1,0),0)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try somthing like the below...

1st MAtch to get the row
2nd match to get the column

=INDEX(array,MATCH(value,A:A,0),MATCH(value,A1:J1, 0))

If this post helps click Yes
---------------
Jacob Skaria


"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)

Hi,

A table lookup takes the following format

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

Whe-
a1:E20 is the full table including header row and column
F1 is the row lookup value
G1 is the column lookup value

Mike

"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)

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)

Hello,

Shorter and non-volatile is
=INDEX(TAB,MATCH(A1,INDEX(TAB,,1),0),MATCH(B1,INDE X(TAB,1,),0))

Regards,
Bernd

This is almost exactly what I'm looking for, just with a slight variation.

I want the function to return four values

The data at the intersection of:
(colval2,Rowval3)
(colval3,Rowval3)
(colval2,Rowval4)
(colval2,Rowval4)

I do not necessarily know the values, or interval, between ColVal2 and ColVal3 (similarly Rowval3 and rowval4) just the value for colval2 and rowval3

Any help would be much appreciated!



Chip Pearson wrote:

The formula below may be longer than others, but it has the advantagethat it
05-Oct-09

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 is not 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)

Previous Posts In This Thread:

On Monday, October 05, 2009 7:34 AM
dp99 wrote:

Looking up data in a table
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 do not know exactly
where these intersect (which you need for H & V lookups)

On Monday, October 05, 2009 7:41 AM
Bernard Liengme wrote:

Looking up data in a table
For info on a two-way lookup see
http://www.contextures.com/xlFunctio...ml#IndexMatch2
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

On Monday, October 05, 2009 7:45 AM
Jacob Skaria wrote:

Try somthing like the below...
Try somthing like the below...

1st MAtch to get the row
2nd match to get the column

=INDEX(array,MATCH(value,A:A,0),MATCH(value,A1:J1, 0))

If this post helps click Yes
---------------
Jacob Skaria


"dp99" wrote:

On Monday, October 05, 2009 7:47 AM
Mike H wrote:

Looking up data in a table
Hi,

A table lookup takes the following format

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

Whe-
a1:E20 is the full table including header row and column
F1 is the row lookup value
G1 is the column lookup value

Mike

"dp99" wrote:

On Monday, October 05, 2009 7:53 AM
Jacob Skaria wrote:

Another way is to use VLOOKUP itself.
Another way is to use VLOOKUP itself. To find the column number use MATCH()

=VLOOKUP(value,array,MATCH(value,A1:J1,0),0)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

On Monday, October 05, 2009 12:08 PM
Chip Pearson wrote:

The formula below may be longer than others, but it has the advantagethat it
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 is not 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 Wednesday, October 07, 2009 9:23 PM
Bernd P wrote:

Hello,Shorter and non-volatile
Hello,

Shorter and non-volatile is
=INDEX(TAB,MATCH(A1,INDEX(TAB,,1),0),MATCH(B1,INDE X(TAB,1,),0))

Regards,
Bernd


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Started with SQLite and Visual Studio
http://www.eggheadcafe.com/tutorials...-sqlite-a.aspx