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...

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...


.

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...


.