MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!