Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is Indexing the right option?
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel instance used with "Export to Microsoft Excel" option in Internet Explorer | Excel Discussion (Misc queries) | |||
Second option button disappears when I make another column | Excel Worksheet Functions | |||
Filter option | Excel Discussion (Misc queries) | |||
No Save As option | Excel Discussion (Misc queries) | |||
XML Data menu option -- not listed / available. How do you fix thi | Excel Discussion (Misc queries) |