Another question on the lookup function! I'm using:

=INDEX($B$2:$D$4,MATCH("WW04",$A$2:$A$4,0),MATCH(" WW03",$B$1:$D$1,0))

I would like to 'autofill' my 2nd lookup_value ("WW03" here) by saying look
at the cell above this one I have just pasted you into. Can you nest
commands like that??

As it stands I am copying this formula into long strings and changing the
two lookup values by hand... It's not awful but if I can take another
shortcut... Well you know..

Thanks!!

Krista




Ah yes, I had just tried that and it finally worked... Thanks so much!

I just saved myself literally weeks on this analysis. Wish I had tried this
days ago....

"Jason Morin" wrote:

You almost have it:

=INDEX($B$2:$D$4,MATCH("WW04",$A$2:$A$4,0),MATCH
("WW03",$B$1:$D$1,0))

To understand the double unary (--)and it's usage, take a
look at:

http://tinyurl.com/5o7lm

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, I'm having some issues with the index, match
function.

I'm trying to query a correlation matrix that has of
course has the same
text values as row headers and column headers. The
matrix values are numbers.

YEAR WW02 WW03 WW04
WW02 0 160.7 40.2
WW03 160.7 0 249.1
WW04 40.2 249.1 0

I need a formula that can give me the distances between
any points eg WW04,
WW03 = 249.1.

Here is my formula:
=INDEX($B$2:$D$4,MATCH(--"WW04",$A$2:$A$4,0),MATCH(--
"WW03",$B$2:$B$5,0))

Since I am combining text lookups and numeric output I
thought the -- was
needed since I was getting #N/A errors. Can you
combine -- with "" specific
values?

Sorry if this is unclear... I'm really stuck and I do
NOT want to look all
these up by hand as I have nearly 65,000 data points...


.

Use a relative address. For example, if you first enter this formula
in G2, instead of "WW03" use G1 (no quotes). Now, if G2 is copied into
H2, the reference to G1 will become H1. If you copy G2 to G3, the
reference to G1 will become G2.

You can also use partial-absolute-relative references. For example, if
you want to always refer to row 1 but want the column to change, when
entering the formula in G2 use G$1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Another question on the lookup function! I'm using:

=INDEX($B$2:$D$4,MATCH("WW04",$A$2:$A$4,0),MATCH(" WW03",$B$1:$D$1,0))

I would like to 'autofill' my 2nd lookup_value ("WW03" here) by saying look
at the cell above this one I have just pasted you into. Can you nest
commands like that??

As it stands I am copying this formula into long strings and changing the
two lookup values by hand... It's not awful but if I can take another
shortcut... Well you know..

Thanks!!

Krista




Ah yes, I had just tried that and it finally worked... Thanks so much!

I just saved myself literally weeks on this analysis. Wish I had tried this
days ago....

"Jason Morin" wrote:

You almost have it:

=INDEX($B$2:$D$4,MATCH("WW04",$A$2:$A$4,0),MATCH
("WW03",$B$1:$D$1,0))

To understand the double unary (--)and it's usage, take a
look at:

http://tinyurl.com/5o7lm

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello, I'm having some issues with the index, match
function.

I'm trying to query a correlation matrix that has of
course has the same
text values as row headers and column headers. The
matrix values are numbers.

YEAR WW02 WW03 WW04
WW02 0 160.7 40.2
WW03 160.7 0 249.1
WW04 40.2 249.1 0

I need a formula that can give me the distances between
any points eg WW04,
WW03 = 249.1.

Here is my formula:
=INDEX($B$2:$D$4,MATCH(--"WW04",$A$2:$A$4,0),MATCH(--
"WW03",$B$2:$B$5,0))

Since I am combining text lookups and numeric output I
thought the -- was
needed since I was getting #N/A errors. Can you
combine -- with "" specific
values?

Sorry if this is unclear... I'm really stuck and I do
NOT want to look all
these up by hand as I have nearly 65,000 data points...


.