Two dimensional lookup
 

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?

Two dimensional lookup
 

Look at Vlookup and Hlookup in help
--
Martin


"CDog" wrote:

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?

Two dimensional lookup
 

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

CDog wrote:
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?

Two dimensional lookup
 

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

"Martin" wrote:

Look at Vlookup and Hlookup in help
--
Martin


"CDog" wrote:

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?

Two dimensional lookup
 

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?

"Rowan Drummond" wrote:

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

CDog wrote:
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?

Two dimensional lookup
 

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

"CDog" wrote in message
...
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?

"Rowan Drummond" wrote:

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

CDog wrote:
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?

Two dimensional lookup
 

Thanks Guys - Works a treat now
Cheers
CDog!

"Biff" wrote:

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

"CDog" wrote in message
...
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?

"Rowan Drummond" wrote:

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

CDog wrote:
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?