Creating a sorted spreadsheet using data from 2 separate sheets

Hello,

I have been trying to tackle an issue, but can't seem to get it right, so I've come to the experts.

I have 2 spreadsheets. The first has 4 columns and the data is sorted correctly, and the second has 24 columns, but the data isn't sorted correctly.

The data in columns C&D from sheet 1 matches the data in columns L&M on sheet 2. What I would like to do is based on those matching columns, create a sheet 3 that takes the data from sheet 2, compares it to the matching data on sheet 1 and sorts it correctly on sheet 3.

Please help, as my eyeballs cannot take doing this process manually any longer.

i think we need to see the sheets

seems to be missing the original sheet 2

Not sure if this is what you mean, but if you put this formula in A1 of Sheet3:

=INDEX(Sheet1!A:A,MATCH(Sheet2!$C2,Sheet1!$L:$L,0))

and copy down and across.. it copies the info in the same order as column C in Sheet2 is listed.

See attached.

The best way to do that and avoid array formulas, is to add helper columns to sheet1 and sheet2, that concatenates the 2 fields together, then index in sheet3 based on those new columns.

So in Sheet1, Y2:

=L2&"_"&M2

copied down

in Sheet2, E2:

=C2&"_"&D2

copied down

Then in Sheet1, A1:

=INDEX(Sheet1!A:A,MATCH(Sheet2!$E2,Sheet1!$Y:$Y,0))

copied down and across.