Formula to pull all data for Top 25

问题:

I've following data set and I've used Index- Large function

=LARGE(INDEX(($A$2:$A$99)*($B$2:$B$99={"x","y"}), , ), ROW(1:1))

... to find out Top 25 of x and y combined. How would I pull the Column that has listed fruits? I can't use Large since it's for Numbers only. I've 20 more columns that have text only. Any thoughts?

apple        100    x  
banana        50    y  
grapes         6    z  
watermelon    89    x  
cantaloupe     5    x  
orange        24    y   

回答1:

You need to adjust the formula you provided for the correct columns first.

=LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))

When that is done, it can be used as matching criteria in a larger formula that also matches column C for x, y.

=IFERROR(INDEX(A$2:A$99, MIN(INDEX(ROW($1:$98)+(($C$2:$C$99<>{"x","y"})+($B$2:$B$99<>LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))))*1E+99, , ))), "")

In the sample image below, that formula would go into E4. Fill both right and down as necessary.

      

If the values in the nmbr column may be duplicated (while still matching {x, y}) then one formula cannot be used for the entire lookup table. Use the following in F4.

=LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))

E4 would be,

=IFERROR(INDEX(A$2:A$99, SMALL(INDEX(ROW($1:$98)+(($C$2:$C$99<>{"x","y"})+($B$2:$B$99<>LARGE(INDEX(($B$2:$B$99)*($C$2:$C$99={"x","y"}), , ), ROW(1:1))))*1E+99, , ), COUNTIF($F$4:$F4, $F4))), "")

Copy and paste E4 to G4 then select E4:G4 and fill down.

      

Note that when transcribing the formula for your own use, ROW(1:98) is the position within A2:A99, not the actual row on the worksheet.