Sort and Match Uneven Columns of Data

[PictureMeRollin]

How can i go about sorting and matching two columns or more of data if the columns are uneven? (See Simplified Pic below) Basically I need the rows in the first two columns to match with the according data in the last two columns. A simple sort will not work because there are not the same numbers in the RA# and AF# fields.

[mhabib]

Assuming your match criteria reside under RA# and AF# columns, you can populate either of the two sets of data using a combination of INDEX and MATCH functions.

Are you familiar with these?

m

[PictureMeRollin]

not really, could you provide an example? I just need to make sure that the ra# and quantity columns match up with the af# and quantity column.
so it would go from

to

[mhabib]

To get your results to appear exactly like you have them in http://www.benrabicoff.com/match2.jpg, you need to have the original RA# List somewhere other than in Columns B & C, as this is where you'd like the final output (sorted list) to appear. To avoid confusion, let's make your sorted RA# list appear in columns H and I rather than B & C. That is, the sorted RA#'s will appear in column I and corresponding quantities in column H.

The following formulas can help you achieve this.


in cell I2, you will need to type in the formula:

=IF(COUNTIF($C:$C,E2)=0,"",VLOOKUP(E2,$C:$C,1,0))


in cell H2, you will need to type in the formula:

=IF(I2="","",INDEX($B:$B,MATCH(E2,$C:$C,0)))


Both the above formulas may be copied down till the last row of data.

HTH

m