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 |
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 |
All times are GMT +1. The time now is 07:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com