• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automating Simple Match and Plug

gupta425

New Member
Hey All,
Would appreciate your help here.
Please see the attached excel file.
My problem statement is rather simple, but I was hoping that someone could guide me here;
The first tab in the file (titled "Data Set") has 4 part types (P1,P3...) listed in the first row. The numbers below in each column are specific to that part type only.
On the second tab in the file (titled " Placement Sheet") I have only the part types in the first row, which are arranged randomly.

My task is to figure out where each part type of the "Data Set", is located on the "Placement Sheet", and subsequently copy the numbers on the "Placement Sheet" under the correct part type.
This is the sample data , but in reality I'm dealing with 100s of part types on the "Placement Sheet" and so wanted to know if this can be done quickly via Index &Match (perhaps using arrays?) or something faster

Your help is much appreciated.
Kind Regards,
Avi
 

Attachments

Peter Bartholomew

Well-Known Member
Assuming this is what you require.

68081

= XLOOKUP(@placement.hdg, data.hdg, dataTable, "null" )
copied across row 2. To get an array to spill to fill the entire table (from cell A2) requires
= IFERROR( INDEX( dataTable, SEQUENCE(3), XMATCH(placement.hdg, data.hdg) ),"null" )
which is closer to @rahulshewale1 .

Future versions of Excel will allow
= LET(
rowNum, SEQUENCE(ROWS(dataTable)),
colNum, XMATCH(placement.hdg, data.hdg),
partData, INDEX( dataTable, rowNum, colNum),
IFERROR( partData,"null" ) )


Note: These are Microsoft 365 solutions which fail in legacy systems.
 
Last edited:

Somendra Misra

Excel Ninja
Hi,

Use below formula on Placement Sheet in A2 and copy down and across.

=INDEX(DataSet!$A$2:$D$4,ROWS(A$2:A2),MATCH('Placement Sheet'!A$1,DataSet!$A$1:$D$1,0))

Regards,
 
Top