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]

Kelly, there is a more efficient solution to your problem:

1. Select the entire range of the original data, including the headers and all information below them.

2. From the Home tab, select "Sort & Filter" > "Custom Sort..."

3. In the dialog box that appears, choose "class" ... "values" ... "A to Z" from the dropdown options.

4. At the top of the box, click "Add Level."

5. In the next three boxes, choose "rank" ... "values" ... "Smallest to Largest" from the dropdown options.

Your data will now be sorted as you want.

 

[kelly mort]

Kelly, there is a more efficient solution to your problem:

1. Select the entire range of the original data, including the headers and all information below them.

2. From the Home tab, select "Sort & Filter" > "Custom Sort..."

3. In the dialog box that appears, choose "class" ... "values" ... "A to Z" from the dropdown options.

4. At the top of the box, click "Add Level."

5. In the next three boxes, choose "rank" ... "values" ... "Smallest to Largest" from the dropdown options.

Your data will now be sorted as you want.

Thanks for the tip and suggestion. But this does not give me what i wanted. I wanna lookup the values based on what i have in L21. I dont want to sort my original data. So when i have in L21 A, only the data with the class A should be filtered in the second table. When it is class B, then show only those with class B, When Blank, show only those with no class, etc. Thanks
Kelly

 

[inactiveuserps07]

Kelly, please include with your sample data set the Column letters and Row numbers. That will allow people here to help you. All formulas rely on cell references, and as shown in your original post, we don't know where your data is located in your sheet (other than the entry cell at L21).

 

[kelly mort]

The first table is A1:N18 and the second table is A23:M31. Thank you again

 

[inactiveuserps07]

Hi, Kelly. I have questions about your setup, but I'll just supply a solution to what you've given (and assuming your headers for the second table run A23:N23):

1. In cell A24, copy this formula:

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

2. Drag-copy this formula across to M24.

3. Select A24:M24 and drag-copy down to A31:M31.

 

[kelly mort]

Yes!!! It has worked just as i wanted it. The only issue here is that when L21 is blank, it does not show the data for those with no class.

 

[inactiveuserps07]

Kelly, it does show the matches for blank L21 on my version. Which version of Excel are you using?

 

[kelly mort]

I am using 2016

 

[inactiveuserps07]

It's possible that you've actually entered a space in L21 instead of actually deleting it to null. To catch either case, try this slightly changed version of the formula, and then let me know if it works:

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

If not, you may actually have spaces in the "class" column of your main data table rather than nulls. Try going to each of those cells and hitting the Delete/Del key.