Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from table
Hi
I have a table as follows blue green yellow 4 jane heidi natalie 3 bruce sharon fred 2 sally paul Ken 1 brendon kevin graham (excuse the layout but you'll get the idea). I want to put a formula in another worksheet than includes blue and 2 (being the column and row headers) and get 'Salley" returned being the contents of the cell at the intersection of the named column and row. Can someone point me in the right direction - I've tried DGet and Index but they don't work as I need them to (unless I'm missing something" Thanks IanB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from table
This is exactly what INDEX does:
=INDEX(B2:D5,match(2,$A$2:$A$5,0),"Blue",$B$1:$D$1 ,0)) Assuming your table including headers is in A1:D5 HTH "Ianb" wrote: Hi I have a table as follows blue green yellow 4 jane heidi natalie 3 bruce sharon fred 2 sally paul Ken 1 brendon kevin graham (excuse the layout but you'll get the idea). I want to put a formula in another worksheet than includes blue and 2 (being the column and row headers) and get 'Salley" returned being the contents of the cell at the intersection of the named column and row. Can someone point me in the right direction - I've tried DGet and Index but they don't work as I need them to (unless I'm missing something" Thanks IanB |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from table
With you table in the range A1:D5
B1:D1 = column headers A2:A5 = row headers A10 = lookup value = blue A11 = lookup values = 2 =VLOOKUP(A11,A1:D5,MATCH(A10,A1:D1,0),0) result = sally Biff "Ianb" wrote in message ... Hi I have a table as follows blue green yellow 4 jane heidi natalie 3 bruce sharon fred 2 sally paul Ken 1 brendon kevin graham (excuse the layout but you'll get the idea). I want to put a formula in another worksheet than includes blue and 2 (being the column and row headers) and get 'Salley" returned being the contents of the cell at the intersection of the named column and row. Can someone point me in the right direction - I've tried DGet and Index but they don't work as I need them to (unless I'm missing something" Thanks IanB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting information from a certain place in one cell | Excel Discussion (Misc queries) | |||
Extracting information from large worksheet | Excel Worksheet Functions | |||
Extracting Information from A Pivot Table | Excel Discussion (Misc queries) | |||
Extracting certain information from cells | Excel Worksheet Functions | |||
Extracting information from records to another sheet automatically | Excel Worksheet Functions |