Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table data
Hello
I have three columns of data which I would like to get into a table. I cant use a pivot table as each column is alpha none are numeric What I am after is some kind of look up function that would look up both the column heading in column 1 and row title in column 2 and then return the data in column 3 Something like below.... Has any body got any ideas? Thanks Raw...... Column Row Data Budget1 Code1 unique1 Budget1 Code2 unique2 Budget1 Code3 unique3 Budget2 Code1 unique4 Budget2 Code2 unique5 Budget2 Code3 unique6 Budget3 Code1 unique7 Budget3 Code2 unique8 Budget3 Code3 unique9 Table...... Budget1 Budget2 Budget3 Code1 unique1 unique4 unique7 Code2 unique2 unique5 unique8 Code3 unique3 unique6 unique9 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table data
Hi Joe,
I am making the following assumptions Raw data is in A1:C10 Table range: E1:H4 Column headers: F1:H1 Row headers: E2:E4 Try this formula in the data section of the table starting in F2 =INDEX($C$2:$C$10, MATCH(1, (F$1 = $A$2:$A$10)*($E2=$B$2:$B$10), 0)) You will need to use Ctrl + Shift + Enter. This creates an ARRAY FORMULA, recognizable by the curly brackets " { } " that automatically appear around it. You never type these curly brackets when entering an array formula Then fill down and across and you should have what you need. Steve DB "Joe" wrote in message ... Hello I have three columns of data which I would like to get into a table. I cant use a pivot table as each column is alpha none are numeric What I am after is some kind of look up function that would look up both the column heading in column 1 and row title in column 2 and then return the data in column 3 Something like below.... Has any body got any ideas? Thanks Raw...... Column Row Data Budget1 Code1 unique1 Budget1 Code2 unique2 Budget1 Code3 unique3 Budget2 Code1 unique4 Budget2 Code2 unique5 Budget2 Code3 unique6 Budget3 Code1 unique7 Budget3 Code2 unique8 Budget3 Code3 unique9 Table...... Budget1 Budget2 Budget3 Code1 unique1 unique4 unique7 Code2 unique2 unique5 unique8 Code3 unique3 unique6 unique9 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table data
Hi, Joe,
You might want to try looking at the help for VLOOKUP and HLOOKUP. I'm not good at explaining, but you can set your "raw data" up on one tab in your workbook, then on another tab, you can put formulas and then copy them to the appropriate cells to have them read your raw data table. Sandi "Joe" wrote: Hello I have three columns of data which I would like to get into a table. I cant use a pivot table as each column is alpha none are numeric What I am after is some kind of look up function that would look up both the column heading in column 1 and row title in column 2 and then return the data in column 3 Something like below.... Has any body got any ideas? Thanks Raw...... Column Row Data Budget1 Code1 unique1 Budget1 Code2 unique2 Budget1 Code3 unique3 Budget2 Code1 unique4 Budget2 Code2 unique5 Budget2 Code3 unique6 Budget3 Code1 unique7 Budget3 Code2 unique8 Budget3 Code3 unique9 Table...... Budget1 Budget2 Budget3 Code1 unique1 unique4 unique7 Code2 unique2 unique5 unique8 Code3 unique3 unique6 unique9 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up/math text | Excel Worksheet Functions | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
changing proportion of chart and data table | Charts and Charting in Excel |