Vertical lookup & crosstabs

[Dick]

Hi,

I'm using crosstabs and vertical lookup a lot lately. Is it possible to
search with vertical lookup not on columnnumber but on columnheader? (This
problem does not occur only with crosstabs).

I want to search on a string in a column with a header. For example string
"254" and month apr.


jan feb mar apr may jun jul
123 1 23 4 2 5 7 8
452 9 12 11 1 4 6 7
254 34 4 4 5 5 7 9
3664 6 5 9 6 7 7 9
2115 4 3 7 9 9 8 6
145 4 7 8 6 8 9 6



Thanks,


C.

 

[zackb]

Hi Dick,

Try to look at an INDEX/MATCH combo. An example would look like this ....

=INDEX($A$1:$L$7,MATCH(254,$A:$A,0),MATCH("apr",$1:$1,0))

Note this takes some things into assumption: Your data does not go past row
7; the values going down column A are numerics, with no leading or trailing
spaces; the values in row 1 are your monthly headers and are textual. If
your monthly values are textual, replace the formula with this ...

=INDEX($A$1:$L$7,MATCH(254,$A:$A,0),MATCH("apr",TEXT(A1:L1,"mmm"),0))

This one, however, must be confirmed with Ctrl + Shift + Enter, often
referred to as a CSE formula for the key sequence confirmed with. Note that
when you confirm this formula correctly, Excel will automatically enclose
the formula in curly brackets { } . These are NOT put in by hand.