• 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)

  • Thread starter Thread starter 3G
  • Start date Start date
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