| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup multiple values
On one worksheet, I have a "table" with a header column & header row, along
with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup multiple values
Hi,
Not sure if this will help you but i assume the following: 1. there are only 3 columns of data in your actual report, similar to your example and 2. the numbers in the table are in running sequence from left to right and not in random sequence. 3. the letter 'A' is at location A1 and number 2100 is at location A2 and so on If so, in your second sheet type the following into the cell cell A1: =SMALL(Sheet1!$A$2:$C$4,ROW()) cell B1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),MATCH (A1,Sheet1!C:C,0),IF(ISERROR(MATCH(A1,Sheet1!A:A,0 )),MATCH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0)))-1 cell C1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),Sheet 1!$C$1,IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),Sheet1!$ B$1,Sheet1!$A$1)) Drag the formula down as required "richzip" wrote: On one worksheet, I have a "table" with a header column & header row, along with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup multiple values
Thank you for the input,
THe data actually is more than 3 columns; and the data throughout the table is random. The actual numbers are not in sequence, and several numbers may also be skipped. The first column label is in cell B4, the first row label is in cell A6. The first cell with data is B6. "ck13" wrote: Hi, Not sure if this will help you but i assume the following: 1. there are only 3 columns of data in your actual report, similar to your example and 2. the numbers in the table are in running sequence from left to right and not in random sequence. 3. the letter 'A' is at location A1 and number 2100 is at location A2 and so on If so, in your second sheet type the following into the cell cell A1: =SMALL(Sheet1!$A$2:$C$4,ROW()) cell B1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),MATCH (A1,Sheet1!C:C,0),IF(ISERROR(MATCH(A1,Sheet1!A:A,0 )),MATCH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0)))-1 cell C1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),Sheet 1!$C$1,IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),Sheet1!$ B$1,Sheet1!$A$1)) Drag the formula down as required "richzip" wrote: On one worksheet, I have a "table" with a header column & header row, along with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup multiple values
Hi,
I tried this formula that I found on a sample size and works. In cell A1 of sheet 2, =SMALL(Sheet1!$B$6:$K$15,ROW(Sheet1!$A1)) In cell B1 of sheet 2, =INDEX(Sheet1!$A$6:$A$15,MATCH(TRUE,COUNTIF(OFFSET (Sheet1!$B$4:$K$4,ROW(Sheet1!$B$6:$K$15)-ROW(Sheet1!$B$4),0,1),A1)0,0)) In cell C1 of sheet 2, =INDEX(Sheet1!$B$4:$K$4,MATCH(A1,INDEX(Sheet1!$B$6 :$K$15,MATCH(TRUE,COUNTIF(OFFSET(Sheet1!$B$6:$K$15 ,ROW(Sheet1!$B$6:$K$15)-ROW(Sheet1!$B$6),0,1),A1)0,0),0),0)) NOTE: for the formula in cell B1 and C1, instead of using the "Enter" key, you will need to use "Ctrl"+"Shift"+"Enter" at the same time. This will add a {} to the formula. It does not work if you key in the curly brackets yourself. Drag the formula down. Change the range as required. Let me know if this helps as this is the first time I tried also. "richzip" wrote: Thank you for the input, THe data actually is more than 3 columns; and the data throughout the table is random. The actual numbers are not in sequence, and several numbers may also be skipped. The first column label is in cell B4, the first row label is in cell A6. The first cell with data is B6. "ck13" wrote: Hi, Not sure if this will help you but i assume the following: 1. there are only 3 columns of data in your actual report, similar to your example and 2. the numbers in the table are in running sequence from left to right and not in random sequence. 3. the letter 'A' is at location A1 and number 2100 is at location A2 and so on If so, in your second sheet type the following into the cell cell A1: =SMALL(Sheet1!$A$2:$C$4,ROW()) cell B1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),MATCH (A1,Sheet1!C:C,0),IF(ISERROR(MATCH(A1,Sheet1!A:A,0 )),MATCH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0)))-1 cell C1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),Sheet 1!$C$1,IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),Sheet1!$ B$1,Sheet1!$A$1)) Drag the formula down as required "richzip" wrote: On one worksheet, I have a "table" with a header column & header row, along with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup multiple values
Hello ..it seems to work beautifully! Thank you very much!
"ck13" wrote: Hi, I tried this formula that I found on a sample size and works. In cell A1 of sheet 2, =SMALL(Sheet1!$B$6:$K$15,ROW(Sheet1!$A1)) In cell B1 of sheet 2, =INDEX(Sheet1!$A$6:$A$15,MATCH(TRUE,COUNTIF(OFFSET (Sheet1!$B$4:$K$4,ROW(Sheet1!$B$6:$K$15)-ROW(Sheet1!$B$4),0,1),A1)0,0)) In cell C1 of sheet 2, =INDEX(Sheet1!$B$4:$K$4,MATCH(A1,INDEX(Sheet1!$B$6 :$K$15,MATCH(TRUE,COUNTIF(OFFSET(Sheet1!$B$6:$K$15 ,ROW(Sheet1!$B$6:$K$15)-ROW(Sheet1!$B$6),0,1),A1)0,0),0),0)) NOTE: for the formula in cell B1 and C1, instead of using the "Enter" key, you will need to use "Ctrl"+"Shift"+"Enter" at the same time. This will add a {} to the formula. It does not work if you key in the curly brackets yourself. Drag the formula down. Change the range as required. Let me know if this helps as this is the first time I tried also. "richzip" wrote: Thank you for the input, THe data actually is more than 3 columns; and the data throughout the table is random. The actual numbers are not in sequence, and several numbers may also be skipped. The first column label is in cell B4, the first row label is in cell A6. The first cell with data is B6. "ck13" wrote: Hi, Not sure if this will help you but i assume the following: 1. there are only 3 columns of data in your actual report, similar to your example and 2. the numbers in the table are in running sequence from left to right and not in random sequence. 3. the letter 'A' is at location A1 and number 2100 is at location A2 and so on If so, in your second sheet type the following into the cell cell A1: =SMALL(Sheet1!$A$2:$C$4,ROW()) cell B1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),MATCH (A1,Sheet1!C:C,0),IF(ISERROR(MATCH(A1,Sheet1!A:A,0 )),MATCH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0)))-1 cell C1: =IF(ISERROR(IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),MAT CH(A1,Sheet1!B:B,0),MATCH(A1,Sheet1!A:A,0))),Sheet 1!$C$1,IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),Sheet1!$ B$1,Sheet1!$A$1)) Drag the formula down as required "richzip" wrote: On one worksheet, I have a "table" with a header column & header row, along with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to do a lookup using multiple values | Excel Worksheet Functions | |||
| Lookup multiple values | Excel Worksheet Functions | |||
| Multiple lookup values and adding multiple rates across together | Excel Worksheet Functions | |||
| Lookup on multiple values | Excel Discussion (Misc queries) | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Hybrid Mode
