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

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

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

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

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