Two dimensional lookup

[Guest]

Trying to get value to return from a table where the row and column reference
intersect and to bring that value back to the previous worksheet.

EG table looks a litle like this
A B C D E F
abc1 10 12 14 16 22 57
abc2 20 22 25 29 34 60
abc3 50 67 85 99 105 110

So say I wanted to bring back the value where abc2 and c intersect = 25

How do I do this?

 

[Guest]

Look at Vlookup and Hlookup in help

 

[Rowan Drummond]

Assuming your data is in A1:G4 and you have the value abc1 in H1 and C
in H2 then try:
=INDEX($A$1:$G$4,MATCH(H1,$A$1:$A$4,0),MATCH(H2,$A$1:$G$1,0))

Hope this helps
Rowan

 

[Guest]

I need it in one equation the reference in the row (abc2) and the column are
sourced from another sheet as to what they are... Its a little more complex
than just using V and H lookup

 

[Guest]

Hi Rowan - thanks for that I think I am getting closer - but I suspect it may
be even a little more complex - The row and column values I am trying to
match came from another work sheet - so I am ending up with a formula that
looks a little like this:

=INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving
Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0))

Now I know the above formula is not quite right but I cannot see where I am
going wrong - the formula gives #N/A error. Any ideas?

 

[Biff]

Hi!

=INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving
Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0))

A couple of things:

It appears that your table is in the range A1:AA362.

It's very inefficient to index the entire sheet when you only need to index
a much smaller portion.

The problem is this portion:

MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)

Try changing it to:

MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0)

And, if row 1 of the table is headers and not really part of the data:

MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0)+1

Biff

 

[Guest]

Thanks Guys - Works a treat now
Cheers
CDog!

Hi!


A couple of things:

It appears that your table is in the range A1:AA362.

It's very inefficient to index the entire sheet when you only need to index
a much smaller portion.

The problem is this portion:

MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)

Try changing it to:

MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0)

And, if row 1 of the table is headers and not really part of the data:

MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0)+1

Biff