Using Index and Match to organize by rank

[Weasel]

My sheet has a bunch of Name's in column D, either the text "Store" or nothing in column E, and a dollar value in column F. I'm trying to figure a way to display the names in order of highest to lowest dollar value based on the criteria that the name must have the text "Store" next to it in column E. If column E is blank then the name would not be included in the ranking. I thought I could use the following code to accomplish this:

{=(INDEX($A$1:$N$10000,MATCH(LARGE(IF($E$1:$E$10000="Store",$F$1:$F$10000-ROW($F$1:$F$10000)/10^5),1),IF($E$1:$E$10000="Store",$F$1:$F$10000-ROW($F$1:$F$10000)/10^5),0),4))}

That formula gives me a N/A result. I appreciate any help in getting this to work whether with this formula or something else

[DonkeyOte]

on a smaller scale with D1:D6 holding Name, E1:E6 holding Store flag and F1:F6 holding dollar amount the following committed to G1 as an array:

=IF(ROW()>COUNTIF($E$1:$E$6,"Store"),"",INDEX($D$1:$D$6,MATCH(LARGE(IF($E$1:$E$6="Store",$F$1:$F$6+(ROW($1:$6)/1000000)),ROW()),$F$1:$F$6+(ROW($1:$6)/1000000),0),1))

copied down to say G6 would give you a listing from high to low (and blank where there were no more "store" entries)

Using over large ranges as you look to be using -- ie 10000 rows will be pretty resource intensive... try to keep your range sizes to a minimum where possible -- if you only have say 1500 rows of data don't set your ranges to 10000 rows -- set to say 2000 rows or think about using dynamic named ranges.

Hope that helps

[Weasel]

It's giving me a blank value. I think it might be due to the fact that the results have to be at the bottom of my sheet so they're starting at row 1000. How can I modify the formula you listed to work for me?

[DonkeyOte]

post back with your ranges in terms of E entries etc... also in what row are you pasting the first of these formulae ?

[Weasel]

My rows of data start at 4 and end at 1025. I then am starting to enter the formula in row 1028. Also, the data is broken up into sections and each section has a header for the rows. So for example, in row 3 column F has "Sales" typed. Then again in row 20 Sales is typed. I'm not sure if this effects anything.

[DonkeyOte]

Let's assume the following:

A1028 holds the RANK position that ROW is to hold, eg A1028 = 1 (A1029 = 2, A1030 = 3 and so on and so forth)
B1028 will hold the below formula (to be copied down to B1029, B1030 etc...)

Again, entered as an array using SHIFT + CTRL + ENTER

=IF($A1028>COUNTIF($E$4:$E$1025,"Store"),"",INDEX($D$4:$D$1025,MATCH(LARGE(IF($E$4:$E$1025="Store",$F$4:$F$1025+(ROW($4:$1025)/1000000)),$A1028),$F$4:$F$1025+(ROW($4:$1025)/1000000),0),1))

Post back ASAP as I want to head off line.

[Weasel]

Some were N/A and some were #NUM!

When I changed those formula's using ISERROR it fixed it and made your formula work. Thanks for your help