double lookup

  • Thread starter Thread starter goss
  • Start date Start date
G

goss

Hi ng
Using xl xp pro

Trying to modify CPearson's Double Lookup.
http://www.cpearson.com/excel/lookups.htm

=OFFSET(Data_1!$A$4,MATCH(A7,MyRange,0),MATCH(C5,MyRange,0))

Where obviously I am trying to refer to data on a separate worksheet which
is a little different than Chip's Ex.
I also named my range "MyRange" instead of using absolute cell references

I am currently receiving #N/A
Can anyone point me in the right direction?

tia
goss
 
The error is here

MATCH(A7,MyRange,0),MATCH(C5,MyRange,0))

match is one dimensional, you have to use something else
if you are looking for A7 in the first column of MyRange

=MATCH(A7,INDEX(MyRange,,1),0)

for the first column and then

=MATCH(C5,INDEX(MyRange,1,),0)

if the row is the top row..


To get the coordinates of a table you have to use something like

=MAX((MyRange=A7)*(ROW(MyRange)))

and

=MAX((MyRange=C5)*(COLUMN(MyRange)))


both entered with ctrl + shift & enter

note that the column and row functions will always count from the first row
and column so you have to offset the data taken that in consideration
 
Another approach to looking up a value which is at the intersection of a
specified row and column is to use XL's "Intersection Operator".
This is simply a "space" between 2 parameters.

The main caveat here is that it works with text labels, so if your list is
so large that you couldn't or wouldn't want to apply a name to each and
every row and column, read no further.

If you'd like to try and experiment with this you should first make sure
"Accept Labels In Formulas" is checked in:
<Tools> <Options> <Calculation> tab.

Check out the following link, just to acquaint yourself with the concept.

http://tinyurl.com/sa10

Now to apply this to a situation similar to the scenario on Chip's web page,
name each row in the list by selecting it, clicking in the name box, typing
the name, and hitting <Enter>.
All = F9:K9
Big = F10:K10
Cats = F11:K11
Die = F12:K12
Early= F13:K13

Name the columns,
Eleven = G8:G13
Twenty= H8:H13
Thirty = I8:I13
Fourty = J8:J13
Fifty = K8:K13

With the lookup values being entered in
F16 AND G16, enter this in H16:

=INDIRECT(F16&":"&F16) INDIRECT(G16&":"&G16)

*Note* the "space" between the 2 Indirects !

You can now enter the name of any row and column in the 2 lookup cells and
have the cell at their intersection returned.
"die" "thirty" yields 113
Also,
"thirty" "die" yields 113
OR ... in *any* cell,
=thirty die
Will yield 113.


HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
so large that you couldn't or wouldn't want to apply a name to each and
every row and column, read no further.
Click to expand...

Assuming a 100x20 matrix with labels for the rows and for the columns - Select
the entire matrix and do Insert / Name / Create / Create names in 'Top Row' &
'Left Column' will do that in one hit.
 
You bring up a good point Ken, *IF* you're starting from scratch !

However, this doesn't work with the scenario on Chip's web page (which I
said I was using), and/or might not be applicable to the OP's list.

In the post that I gave the link to, the pertinent phrase was "unique text".

The top row of Chip's list (matrix), contains *no* "unique text", just
numbers.
<Insert> <Name> <Create> will not work !
I don't know the make-up of the OP's list.

To repeat, your point is well taken when creating a list with this procedure
in mind from the start.
If the top row of Chip's list was created with the names (eleven, twenty,
thirty, ... etc.), instead of the digits, and the first column contained
names instead of just letters, <Insert> <Name> <Create> will work fine.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


Ken Wright said:
so large that you couldn't or wouldn't want to apply a name to each and
every row and column, read no further.
Click to expand...

Assuming a 100x20 matrix with labels for the rows and for the columns -
Select
the entire matrix and do Insert / Name / Create / Create names in 'Top Row'
&
'Left Column' will do that in one hit.
 
Back
Top