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