Is Indexing the right option?

S

Shaun Martin

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
 
N

N Harkawat

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)
 
Top