MrExcel Publishing
Your One Stop for Excel Tips & Solutions

More on Intersection Lookups


Posted by Fizzy on March 22, 2001 2:37 AM

Gee this board is a real inspiration! Has got me to play around with other combinations (remember I am an Excel novice).

In addition to using:

=INDEX($A$1:$D$14,MATCH(F3,$A$1:$A$14,0),MATCH(G3,$A$1:$D$1,0))

have found that:

=VLOOKUP(F3,$A$1:$D$14,HLOOKUP(G3,$B$1:$D$2,2,FALSE),FALSE)

works as well (when you add a hidden row (2 in this example) and populate cells with 1,2,3,....

F3 and G3 are the lookup values

Vlookup and Hlookup combinations assume that the lookup values are in left-most cols and top-most rows however.

Using Mark's method (=aaa large)still can't see how I would enter lookup values in F3 and G3.

again thx for the inspiration ....

F


Posted by Mark W. on March 22, 2001 3:59 AM

> Using Mark's method (=aaa large)still can't see
> how I would enter lookup values in F3 and G3.

See 12599.html

Posted by Loren on March 22, 2001 4:55 AM


Mark and Celia-are you suggesting your formula could replace
Index and Match combinations? I am like Fizzy, I cannot
understand how to enter lookup values in cells, even after
I looked at the previous messages.

Posted by Celia on March 22, 2001 5:23 AM


Loren
You might find it useful to look at Intersection.xls at :-
(broken link)
Celia

Posted by Mark W. on March 22, 2001 6:23 AM

Have you tried using =INDIRECT(ref&" "&ref) where
"ref" are cell references containing text representations
of names that were defined using the
Insert Name Create... menu command?

Posted by Loren on March 22, 2001 8:12 AM

Intersection Lookups; not there yet

Tried that and it returns #VALUE! Now what?

Posted by Mark W. on March 22, 2001 9:03 AM

Re: Intersection Lookups; not there yet

Not enough info to diagnosis. Care to share your
formula and data layout?

Posted by Loren on March 22, 2001 9:29 AM

Re: Intersection Lookups; not there yet


It is just as you gave us 2 days ago, with A,AA,AAA,B in the column.
Small, med, large as top row, then data, then Insert Name Create.
I verified the individual ranges and the =AAA large works.
=INDIRECT(A5&" "&C1) which would enable me to lookup a variety
of cells, gives me #VALUE! Gotta be getting close.

Posted by Mark W. on March 22, 2001 3:59 PM

Most Excellent Observation! Aladin, you'll enjoy this one too.

It turns out that =INDIRECT(A5&" "C1) doesn't produce
consistent results! You must use =INDIRECT(A5) INDIRECT(C1).

I didn't notice this discrepancy earlier. While
testing this solution I entered =INDIRECT(A5&" "C1)
into a cell in column B, C or D. What I didn't
realize was that =INDIRECT(A5&" "C1) was returning the
array, {33,32,37}. Excel does it's own implicit
intersecting when you enter a formula perpendicular to
the array it references. So if =INDIRECT(A5&" "C1) was
entered into a cell in column B it returned 33; in
column C it returned 32; and in column D it returned
37. But, if you put the formula anywhere else it
returned the #VALUE! error. If you'll check the
troubleshooting tips for this error message you'll
notice that it occurs when you fail to enter an
array formula using Control+Shift+Enter. If you
do enter it as an array formula to the left of the
data table it works properly, but if you enter it
as an array formula in a single cell in columns
B, C or D it returns 33 -- the 1st value in the array
{33,32,37}.

As I stated above if you use =INDIRECT(A5) INDIRECT(C1)
you'll avoid this difficulty altogether.

Thank you for being so persistent. It's been a long
time since I've encountered this behavior, a nice
refresher, and an enjoyable troubleshooting session.
Hope you learned a bit about troubeshooting yourself! : )

Posted by Loren on March 23, 2001 5:04 AM

Allllll Righty! Thanks Mark & everyone.