index and match formula help needed

[kelly mort]

INDEX NAME val1 val2 val3 val4 val5 val6 val7 val8 val9 total rank class
1 one 42 45 45 50 45 38 53 53 68 439 4 A
2 two 84 95 90 84 78 93 88 78 61 751 3 B
3 three 85 92 88 83 81 88 91 84 63 755 2 B
4 four 80 86 95 90 88 94 85 85 73 776 1 A
5 five 86 86 81 95 78 99 88 89 73 775 1 C
6 six 60 66 78 77 75 73 66 64 64 623 3 D
7 seven 77 76 76 77 53 53 74 73 65 624 2 D
8 eight 50 50 50 50 50 50 50 50 56 456 4 C
9 nine 86 86 81 95 78 99 88 88 73 774 2 C
10 ten 56 64 80 87 69 87 75 70 47 635 3 A
11 eleven 39 44 83 84 72 91 69 99 71 652 2 A
12 twelve 87 74 78 62 79 82 80 81 73 696 1 D
13 thirteen 92 97 70 90 63 90 60 76 57 695 3 C
14 fourteen 89 94 94 94 94 94 100 100 73 832 1 B
15 fifteen 86 99 80 60 96 80 81 67 67 716 2
16 sixteen 77 80 57 62 66 44 56 65 68 575 3
17 seventeen 77 77 77 77 89 88 94 77 67 723 1


A

INDEX NAME val1 val2 val3 val4 val5 val6 val7 val8 val9 total rank
1 four 80 86 95 90 88 94 85 85 73 776 1
2 eleven 39 44 83 84 72 91 69 99 71 652 2
3 ten 56 64 80 87 69 87 75 70 47 635 3
4 one 42 45 45 50 45 38 53 53 68 439 4
5
6
7
8

So i have a dada set as above. The data starts from A1. Then in L21 I have the letters A B C D or even blank. So i want results appear as in the second table. That is since i have A in L21 show only the data for those in class A. I hope you are getting the picture.

This is the formula i used before, when everything was together. Now i have introduced these classes and i am a bit confused.

=Index(B$2:B$18,Match($A24,$M$2:$M$18,0))

Thanks a lot for reading.
Kelly

 

[kelly mort]

Kelly, using $A24 (or $D24) won't work, because the formula itself is in $A24, which will just cause a circular reference error.

Yes, i have cleared the formula in A24 down. Then i filled there with the numbers 1 2 3 4 etc. And it worked somehow. Now when i tried it in my real project, the blanks are not showing because i think there is a zero in there instead because that column is filled with a formula. I cant tell what value is in the cells for now.

 

[kelly mort]

Okay i think i have figured out what was happening. The formula is pointing to cells that look blank but were not. After clearing the blank looking cells, it seems working Good now. Haha. Thanks again.
Kelly

 

[inactiveuserps07]

Glad it has worked out, Kelly. (And now I understand what you've done with Column A of your second table. You really could accomplish that by just filling in the "rank" column in the second table with 1; 2; 3; 4 and using that as the reference in the formula for the other columns, since that's what shows up there in the end anyway. This would allow you to keep the Column A info there.

 

[kelly mort]

Glad it has worked out, Kelly. (And now I understand what you've done with Column A of your second table. You really could accomplish that by just filling in the "rank" column in the second table with 1; 2; 3; 4 and using that as the reference in the formula for the other columns, since that's what shows up there in the end anyway. This would allow you to keep the Column A info there.

Guess what, i met bigger problems in the process of the way i wanted it so i came back to use the ROW()-x and now i am cool going. I really appreciate your time and patience with me.
Kelly

 

[inactiveuserps07]

Quite welcome, Kelly.

 

[kelly mort]

NAME val1 val2 val3 val4 val5 val6 val7 val8 val9 total rank
one 42 45 45 50 45 38 53 53 68 439 4
four 80 86 95 90 88 94 85 85 73 776 1
ten 56 64 80 87 69 87 75 70 47 635 3
eleven 39 44 83 84 72 91 69 99 71 652 2

I want to show the second table as above. The names in alphabetical order. I cannot figure out how to tweak the previous formula. I am here again. I said i will be back when i am challenged. Haha.

Note; When i say alphabetical order, i mean the one the comes first from top to bottom. The names i have here does not seem to explain the alphabetical order properly. Thanks again

 

[inactiveuserps07]

Hi, Kelly. What you are now trying to do is not a "tweak." An entirely different approach and formula is required, because you won't have the unique rank to help find anything.

Here is a new formula that can be copied into all cells in the lower table in place of the previous one:

=IF(COUNTIF($N$2:$N$18,IF($L$21="","",$L$21))>=ROW()-23,INDIRECT(ADDRESS(SUMPRODUCT(LARGE(($N$2:$N$18=IF($L$21="","",$L$21))*ROW($N$2:$N$18),COUNTIF($N$2:$N$18,IF($L$21="","",$L$21))+1-(ROW()-23))),COLUMN())),"")

 

[inactiveuserps07]

Actually, that last formula can be shortened just a bit to this:

=IF(COUNTIF($N$2:$N$18,IF($L$21="","",$L$21))>=ROW()-23,INDIRECT(ADDRESS(SUMPRODUCT(LARGE(($N$2:$N$18=$L$21)*ROW($N$2:$N$18),COUNTIF($N$2:$N$18,IF($L$21="","",$L$21))+1-(ROW()-23))),COLUMN())),"")

 

[inactiveuserps07]

Hi again, Kelly. I must be tired this morning. Here is another shorter version of the new formula:

=IFERROR(INDIRECT(ADDRESS(SUMPRODUCT(LARGE(($N$2:$N$18=$L$21)*ROW($N$2:$N$18),COUNTIF($N$2:$N$18,IF($L$21="","",$L$21))+1-(ROW()-23))),COLUMN())),"")

 

[kelly mort]

Wow, thanks for the time once again. I travel for a while that kept me this long. Haha . It is working perfect.
Kelly.

Ps. I may be back again. Lol