• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Formula Question (Simliar to Formula Forensics 3)

3G

3G

Member
I'm having some issues with an array formula, similar to the one Luke did in Formula Forensics 3. Good ole =INDEX(SMALL(IF)

Here goes:
I've got a list of data that is ranked based on shortest to longest. It is NOT in sort order, but, has a value of 1, 2, 3, 4, or 5 (K28-K32). This sort order can change as the times of the data changes (i.e. 1st may become 2nd, 3rd, etc). Same range/etc, just that K28:K32 may not always be in the same order, but will have the same values.

I'm trying to match K28:K32 to a small table I have for a chart, which has 1, 2, 3, 4, 5 (V36:V40)in Rank/Sort order, and populate the table with the values in N28:N32. Here's my initial stab:

=INDEX($N$28:$N$32,SMALL(IF($V40=$K$28:$K$32,ROWS($N$1:N1)),ROW($N$1)))

I'm getting the right values from the Index, but, they are NOT in the same order as the rank sort, but rather just as they are in K29:K32 (out of sort order).

Any ideas?
 
3G

Can you post a sample file with a few examples to hopefully clarify your requirements
 
Hi ,

I am not sure I have understood you , but try the following :

=INDEX($N$28:$N$32,LARGE(IF($V36=$K$28:$K$32,ROW($N$1:$N$5)),ROW($N$1)))

This is to be entered as an array formula , using CTRL SHIFT ENTER. Copy down as needed.

Narayan
 
It looks like the key change that needed to be made was ROW($N$1:$N$5)). The Result Array of the IF function needs to be the same length as the Criteria Range
 
Thanks for the feedback on this guys. I forgot to check back. I will institute this formula and let you know.

Much appreciated!
 
Ok. I've rearranged my data and have the following formula:
=INDEX(Table11[Ticket '#],LARGE(IF($AM$11=$X$3:$X$74,ROW($X$3:$X$74)),ROW($N1)))

AM11:AM15 are the values 1, 2, 3, 4, 5. T
X3:X74 is the Ranking (1-70). I want to extract the ticket number (Table 11 data) into AN11:AN15 so it's next to it's ranking...essentially cutting out the top five.

The formula works when entered, but, gives #N/A when Ctrl+Shift+ent.
 
If I understood that right, the range X3:X74 has a bunch of numbers indicating the rank (so, 1-70). AM1:AM5 contain the numbers 1 - 5.
If that is correct, I think this is actually a straight-up lookup type problem. Should just do:
=INDEX(Table11[Ticket '#],MATCH($AM11,$X$3:$X$74,0))
 
You know Luke...it dawned on me during my lunch that I might be overreaching. Indeed! Thanks buddy :). My orignial intent was to just use the times and rank those, but, since I actually added a "rank" column, the iNdex/Match is la bomba.
 
You must log in or register to reply here.
Back
Top