Matrix Query Part II - lookup value

[Guest]

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

 

[Tushar Mehta]

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