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

 

[inactiveuserps07]

In looking at the data in your original post above, it does seem that you have spaces in what looks like your "empty" cells in the "class" column of your main data. A space does not match a null. So you'll want to go to each "empty" class cell and hit the Delete/Del key as I mentioned above. Then the bottom table should populate when you hit Delete/Del in L21.

 

[kelly mort]

Oh okay, in column m, i pasted the data there and i think i pasted something there that was not an empty cell. I clear everything and it worked like a charm. Haha. Thank you very much.

I have a question: why - 23?

 

[inactiveuserps07]

Your bottom table starts to populate in Row 24. So by subtracting 23 (-23), we get "1" and that is being used to find the match where "rank" is 1. As you move down to the next row, Row 25, subtracting 23 will give us 2, so that will be used to find the record match where "rank" is 2, etc.

 

[kelly mort]

Oh okay so what if i want input the numbers 1 2 3 etc in A24 down so the match point to them there instead? Because in my real project i will want to enter those numbers manually.

 

[inactiveuserps07]

If you want to enter the numbers manually the original formula ...

=IFERROR(INDIRECT(ADDRESS(SUMPRODUCT(--($M$2:$M$18=ROW()-23)*($N$2:$N$18=$L$21)*(ROW($N$2:$N$18))),COLUMN()))&"","")

would have the red part replaced with whatever rank number you're trying to find for that row, like this:

=IFERROR(INDIRECT(ADDRESS(SUMPRODUCT(--($M$2:$M$18=1)*($N$2:$N$18=$L$21)*(ROW($N$2:$N$18))),COLUMN()))&"","")

But that doesn't seem very efficient to me.

Wherever your second data table winds up starting, just use "ROW()-x", where x is 1 less than the row where your data starts to populate.

Of course, it's up to you. I just always have an eye toward efficiency and using the natural layout of Excel to do the work where possible.

 

[kelly mort]

($M$2:$M$18=$D24) is what i am trying to use. So that it will pick the numbers there. For the layout that i have at the moment, that will save me from a lot of dangers. Haha. I am really grateful. I will come back when i have a challenge. Regards.
Kelly

 

[inactiveuserps07]

Kelly, if you use a static absolute value like =$D24, every row of the second table will just populate with the same record over and over.

Hopefully, you understand the formula enough, or will, so that you can adapt it to your needs.

 

[kelly mort]

Yes so when i copy it down, will get $D25 where i will get the number 2 then d26 will give me 3 etc.

 

[kelly mort]

Oh sorry it will be $A24 not $D24.

 

[inactiveuserps07]

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