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