Hi,

Appreciate any responses in advance, for your time and help. I have the
following data but many more categories:

Consultant Manager VP
UK 100 150 200
Germany 125 175 225
France 90 140 190

In another worksheet, I have a cell with column A as a "list" and the next
cell with row 1 as a "list", giving for example UK Manager. How can I then
get the insection value (150) populated? I have tried indexing but it doesnt
work as I still need to defne the specific column and row, I need to get it
to look up the option choosen..

Apologises if the above question is not correctly phrased..

thanks again

Shaun :-)
England

given your example in data range A1:D4
and
cell A6 holds the "country" data and cell B6 holds the "position"(vp,manager
etc)
=HLOOKUP(B6,$B$1:$D$4,MATCH(A6,$A$1:$A$4,0),0)
will pick up the intersection of country and the position

However if your data is just in a single cell separate by a space first
country then space and then postion the above formula can be modified to as
follow:-
=HLOOKUP(RIGHT(A6,LEN(A6)-SEARCH(" ",A6)),$B$1:$D$4,MATCH(LEFT(A6,SEARCH("
",A6)-1),$A$1:$A$4,0),0)


"Shaun Martin" <Shaun wrote in message
...
Hi,

Appreciate any responses in advance, for your time and help. I have the
following data but many more categories:

Consultant Manager VP
UK 100 150 200
Germany 125 175 225
France 90 140 190

In another worksheet, I have a cell with column A as a "list" and the next
cell with row 1 as a "list", giving for example UK Manager. How can I then
get the insection value (150) populated? I have tried indexing but it
doesnt
work as I still need to defne the specific column and row, I need to get
it
to look up the option choosen..

Apologises if the above question is not correctly phrased..

thanks again

Shaun :-)
England

Try this

=INDEX(B2:D4,MATCH("UK",A2:A4,0),MATCH("Manager",B 1:D1,0))

--
HTH

Bob Phillips

"Shaun Martin" <Shaun wrote in message
...
Hi,

Appreciate any responses in advance, for your time and help. I have the
following data but many more categories:

Consultant Manager VP
UK 100 150 200
Germany 125 175 225
France 90 140 190

In another worksheet, I have a cell with column A as a "list" and the next
cell with row 1 as a "list", giving for example UK Manager. How can I then
get the insection value (150) populated? I have tried indexing but it
doesnt
work as I still need to defne the specific column and row, I need to get
it
to look up the option choosen..

Apologises if the above question is not correctly phrased..

thanks again

Shaun :-)
England

thank you for your time and effort :-)

"N Harkawat" wrote:

given your example in data range A1:D4
and
cell A6 holds the "country" data and cell B6 holds the "position"(vp,manager
etc)
=HLOOKUP(B6,$B$1:$D$4,MATCH(A6,$A$1:$A$4,0),0)
will pick up the intersection of country and the position

However if your data is just in a single cell separate by a space first
country then space and then postion the above formula can be modified to as
follow:-
=HLOOKUP(RIGHT(A6,LEN(A6)-SEARCH(" ",A6)),$B$1:$D$4,MATCH(LEFT(A6,SEARCH("
",A6)-1),$A$1:$A$4,0),0)


"Shaun Martin" <Shaun wrote in message
...
Hi,

Appreciate any responses in advance, for your time and help. I have the
following data but many more categories:

Consultant Manager VP
UK 100 150 200
Germany 125 175 225
France 90 140 190

In another worksheet, I have a cell with column A as a "list" and the next
cell with row 1 as a "list", giving for example UK Manager. How can I then
get the insection value (150) populated? I have tried indexing but it
doesnt
work as I still need to defne the specific column and row, I need to get
it
to look up the option choosen..

Apologises if the above question is not correctly phrased..

thanks again

Shaun :-)
England

thank you for your time and effort :-)

"Bob Phillips" wrote:

Try this

=INDEX(B2:D4,MATCH("UK",A2:A4,0),MATCH("Manager",B 1:D1,0))

--
HTH

Bob Phillips

"Shaun Martin" <Shaun wrote in message
...
Hi,

Appreciate any responses in advance, for your time and help. I have the
following data but many more categories:

Consultant Manager VP
UK 100 150 200
Germany 125 175 225
France 90 140 190

In another worksheet, I have a cell with column A as a "list" and the next
cell with row 1 as a "list", giving for example UK Manager. How can I then
get the insection value (150) populated? I have tried indexing but it
doesnt
work as I still need to defne the specific column and row, I need to get
it
to look up the option choosen..

Apologises if the above question is not correctly phrased..

thanks again

Shaun :-)
England