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.
