Difficult matrix query
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...
|