仅用Excel公式对含重复值的3列（2数值1文本）降序排序求助

Hey there! I see you're struggling with getting formula-based sorting to work without hitting #NUM! or #VALUE! errors—let's fix that. From your sample data, it looks like you want to sort primarily by Doc Ref (Column A) in descending order, then by A-Ref (Column B) also descending. Here's a solid, error-free approach using only Excel formulas:

Step 1: Create a Helper Column for Sort Logic #

First, add a helper column (let's use Column E) to generate a sort key that combines your numeric columns into a format Excel can easily sort. Since we want descending order, we'll flip the numbers to negatives so higher original values come first when sorted alphabetically:

= -A2 & "-" & -B2

For your first row, this will give you -3904--1234—it looks odd, but it works because when sorted from Z to A, the largest original numbers will appear first.

Step 2: Pull Sorted Values with INDEX + MATCH #

Now use INDEX and MATCH to grab the sorted data into new columns (let's start at Column F for sorted Doc Ref, G for A-Ref, H for Column C):

Sorted Doc Ref (Column F):

= INDEX($A$2:$A$6, MATCH(LARGE($E$2:$E$6, ROWS($F$2:F2)), $E$2:$E$6, 0))

Drag this down to fill all rows. The ROWS($F$2:F2) part counts how many rows we've filled so far, so it grabs the 1st largest, then 2nd, etc., sort key.

Sorted A-Ref (Column G):

= INDEX($B$2:$B$6, MATCH(LARGE($E$2:$E$6, ROWS($G$2:G2)), $E$2:$E$6, 0))

Sorted Column C (Text Values):

= INDEX($C$2:$C$6, MATCH(LARGE($E$2:$E$6, ROWS($H$2:H2)), $E$2:$E$6, 0))

Step 3: Handle Duplicate Values (If Needed) #

If you ever have duplicate values in both A and B columns, tweak the helper column to include the row number for uniqueness:

= -A2 & "-" & -B2 & "-" & ROW(A2)

This ensures even identical A/B pairs have unique sort keys, preventing matching errors.

Why You Were Getting Errors Before #

• #NUM!: This usually pops up when functions like LARGE or RANK can't find valid values—maybe you were trying to rank more rows than exist, or mixing text (like Column C) into a numeric ranking formula.
• #VALUE!: This happens when a function gets the wrong data type (e.g., trying to do math on text from Column C). By using a helper column that only uses numeric data, we avoid this conflict entirely.

Give this a try—it should work smoothly with your sample data and avoid those frustrating errors!

内容的提问来源于stack exchange，提问作者Ansh