Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |