Looking up data in a table

D

dp99

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

Jacob Skaria

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
 
M

Mike H

Hi,

A table lookup takes the following format

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

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

Mike
 
J

Jacob Skaria

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
 
C

Chip Pearson

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

Bernd P

Hello,

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

Regards,
Bernd
 
L

Lepista Lepista

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 advantag
that it uses only a single range reference for the lookup table
including row/column lookup values. If you name the lookup tabl
(including row headers on the left and column headers on the top)
"Tab", you can us

=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 B
is the value to look up in the top row of Tab. So if you have dat
like the following named Tab

ColVal1 ColVal2 ColVal
RowVal
RowVal
RowVal3 .... data ....
RowVal

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

If a value is not found, the result is #N/A

Cordially
Chip Pearso
Microsoft Most Valuable Professiona
Excel Product Group, 1998 - 200
Pearson Software Consulting, LL
www.cpearson.co
(email on web site)

Previous Posts In This Thread:

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

Re: Looking up data in a table
For info on a two-way lookup se
http://www.contextures.com/xlFunctions03.html#IndexMatch
best wishe
-
Bernard V Liengm
Microsoft Excel MV
http://people.stfx.ca/bliengm
remove caps from email

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

1st MAtch to get the ro
2nd match to get the colum

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

If this post helps click Ye
--------------
Jacob Skari

:

RE: Looking up data in a table
Hi

A table lookup takes the following forma

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

Where:
a1:E20 is the full table including header row and colum
F1 is the row lookup valu
G1 is the column lookup valu

Mik

:

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 Ye
--------------
Jacob Skari

:

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 advantag
that it uses only a single range reference for the lookup table
including row/column lookup values. If you name the lookup tabl
(including row headers on the left and column headers on the top)
"Tab", you can us

=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 B
is the value to look up in the top row of Tab. So if you have dat
like the following named Tab

ColVal1 ColVal2 ColVal
RowVal
RowVal
RowVal3 .... data ....
RowVal

and A1 contains RowVal3 and B1 contains ColVal2, the formula wil
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)

Hello,Shorter and non-volatile
Hello,

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

Regards,
Bernd


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top