|
LostinMaine
Nov 11, 2010, 4:11 PM
Post #1 of 9
(977 views)
Shortcut
Registered: May 8, 2007
Posts: 539
|
I know there are some Excel gurus out there who can help with this. I have used 2-way lookup functions in the past without a problem, but for some reason I can't get this one to return any value. Here's a screenie:
Any suggestions of what I am overlooking? I've tried using absolute references for the table and the input variables with no additional success. I want to force cells O9 and O10 to be drop down menus, but I couldn't return an INDEX value with those, either.
Any help is appreciated!
|
|
|
|
|
LostinMaine
Nov 11, 2010, 4:22 PM
Post #2 of 9
(969 views)
Shortcut
Registered: May 8, 2007
Posts: 539
|
never mind. Brain fart. I was referencing the entire array when I only needed to reference the rows and columns with the match function. I love stupid mistakes!
|
|
|
|
|
imnotclever
Nov 11, 2010, 5:28 PM
Post #3 of 9
(960 views)
Shortcut
Registered: Sep 8, 2003
Posts: 10000
|
=INDEX(G10:L14, MATCH(O9,F9:F14,0),MATCH(O10,F9:L9,0))
|
|
|
|
|
imnotclever
Nov 11, 2010, 5:29 PM
Post #4 of 9
(958 views)
Shortcut
Registered: Sep 8, 2003
Posts: 10000
|
LostinMaine wrote: never mind. Brain fart. I was referencing the entire array when I only needed to reference the rows and columns with the match function. I love stupid mistakes!
d'oh!
|
|
|
|
|
Toast_in_the_Machine
Nov 12, 2010, 12:49 PM
Post #5 of 9
(929 views)
Shortcut
Registered: Sep 12, 2008
Posts: 5208
|
imnotclever wrote: LostinMaine wrote: never mind. Brain fart. I was referencing the entire array when I only needed to reference the rows and columns with the match function. I love stupid mistakes! d'oh!
Yet somehow, I suspect, we'll still be posting suggestions to this thread for a while.
|
|
|
|
|
imnotclever
Nov 12, 2010, 12:55 PM
Post #6 of 9
(924 views)
Shortcut
Registered: Sep 8, 2003
Posts: 10000
|
Toast_in_the_Machine wrote: imnotclever wrote: LostinMaine wrote: never mind. Brain fart. I was referencing the entire array when I only needed to reference the rows and columns with the match function. I love stupid mistakes! d'oh! Yet somehow, I suspect, we'll still be posting suggestions to this thread for a while.
I was waiting for somebody to post up some sort of easier / quicker way of doing it.
|
|
|
|
|
Toast_in_the_Machine
Nov 12, 2010, 1:01 PM
Post #7 of 9
(921 views)
Shortcut
Registered: Sep 12, 2008
Posts: 5208
|
imnotclever wrote: Toast_in_the_Machine wrote: imnotclever wrote: LostinMaine wrote: never mind. Brain fart. I was referencing the entire array when I only needed to reference the rows and columns with the match function. I love stupid mistakes! d'oh! Yet somehow, I suspect, we'll still be posting suggestions to this thread for a while. I was waiting for somebody to post up some sort of easier / quicker way of doing it.
My first thought was to run a snippet of VB code I keep on hand called "de-pivot". But I think, for getting the data out as it is, your method is about as direct a way as I can think of.
|
|
|
|
|
Toast_in_the_Machine
Nov 12, 2010, 2:09 PM
Post #8 of 9
(912 views)
Shortcut
Registered: Sep 12, 2008
Posts: 5208
|
imnotclever wrote: Toast_in_the_Machine wrote: imnotclever wrote: LostinMaine wrote: never mind. Brain fart. I was referencing the entire array when I only needed to reference the rows and columns with the match function. I love stupid mistakes! d'oh! Yet somehow, I suspect, we'll still be posting suggestions to this thread for a while. I was waiting for somebody to post up some sort of easier / quicker way of doing it.
It can be done quickly with DGET. I'm at the start of an all day meeting, so I'll post the exact formula later.
|
|
|
|
|
Toast_in_the_Machine
Nov 18, 2010, 2:01 AM
Post #9 of 9
(872 views)
Shortcut
Registered: Sep 12, 2008
Posts: 5208
|
Steps for alternate method:
Change F9 to be "Orientation"
O8 ="Orientation"
O9 = value of oreintation
O11 then becomes
=DGET(F9:L15,O10,O8:O9)
Sorry for the delay in posting.
|
|
|
|
|
|
