Match Formula
 

My data comes in like this (note that each line of data may have the data in
a different order). Table starts on Sheet1:A1

Data Table

A !9 202 !35 D !49 NFS1
B !49 NFS1 !35 D !9 202
C !9 201 !35 D !49 TBD
D !9 203 !49 NFS1 !35 D

I am trying to create a table that look like so. Table starts on Sheet2:A1

New Table
!9 !35 !49
A 202 D NFS1
B 202 D NFS1
C 201 D TBD
D 203 D NFS1

is it possible to have a formula in the new table (B2:D5) that will find the
data in the Data Table that corresponds to the values in ColA and Row 1 in
the New Table.

Thank you in advance.

Assumptions:

Sheet1!A1:G4 contains your data table

Sheet2!A2:A5 contains A, B, C, and D

Sheet2!B1:D1 contains !9, !35, and !49

Formula:

Sheet2!B2, copied down and across:

=INDEX(Sheet1!$A$1:$G$4,MATCH($A2,Sheet1!$A$1:$A$4 ,0),MATCH(B$1,INDEX(She
et1!$A$1:$G$4,MATCH($A2,Sheet1!$A$1:$A$4,0),0),0)+ 1)

Hope this helps!

In article ,
"carl" wrote:

My data comes in like this (note that each line of data may have the data in
a different order). Table starts on Sheet1:A1

Data Table

A !9 202 !35 D !49 NFS1
B !49 NFS1 !35 D !9 202
C !9 201 !35 D !49 TBD
D !9 203 !49 NFS1 !35 D

I am trying to create a table that look like so. Table starts on Sheet2:A1

New Table
!9 !35 !49
A 202 D NFS1
B 202 D NFS1
C 201 D TBD
D 203 D NFS1

is it possible to have a formula in the new table (B2:D5) that will find the
data in the Data Table that corresponds to the values in ColA and Row 1 in
the New Table.

Thank you in advance.