Mark's intersection lookup
Posted by Loren on March 21, 2001 7:41 AM
The formula, =AAA Large , will return the value, 26.
I got this to work ok. BUT what if =AAA Large, instead of
being typed, points to cells, so I can look up a column
of AAA names and large name combinations? thanks
Posted by Mark W. on March 21, 2001 8:04 AM
Then you'd need to Create Names as descibed at
12499.html .
Once these names are created you can then use
cell references such as =INDIRECT(A12&" "&B11).
As I warned earlier once you've created names
for this purpose you should refrain from re-sorting
your data or at least be willing to re-Create the
names in order to re-reference them after the sort.
A little trick I use when I don't every want to
sort data is to enter that data as an array constant
(e.g., {={32,43,35;39,16,44;16,44,26;33,32,37}}) then
Excel will prohibit the sort attempt.
Posted by Celia on March 21, 2001 8:09 AM
Loren
The space character in a formula works as an intersection key. It can be used with cell refs or named ranges.
For example, Marks formula "=AAA Large" could also have been entered as "=4:4 D:D"
Another example :-
The formula "=Sum(B1:C10 A2:D3)" would produce the sum of cells B2:C3.
Celia
Posted by Mark W. on March 21, 2001 8:12 AM
Celia, good point... and while we're on the topic
try:
=1:1 2:2
You'll get an error value that you don't see too
often!
Posted by Mark W. on March 21, 2001 8:15 AM
...It is for this reason that if your label
values are explictly entered in cells you
can't use =A12 B11 .
Posted by Celia on March 21, 2001 8:16 AM
Of course there will be an error. Two rows cannot intersect.
Posted by Celia on March 21, 2001 8:21 AM
Of course you can't. The space is an INTERSECTION key. Two separate cells can't intersect!
It's a bit like dividing by zero - it can't be done - which is OK providing you understand that!
Posted by Mark W. on March 21, 2001 8:22 AM
At least not...
...in this space-time continuum! ; )
Posted by Celia on March 21, 2001 8:25 AM
That's right!
