Specify search

[k0st4din]

Hello
I have a eXercise Becoming entangled with one which is trying to cope, but something eludes me
Here the task of creating a data table A - number B - name, C - Family, D - year follow F-scholarship to fill in at least 8 students.
then
1 to derive the name of the student with highest scholarship
2 to display a list of students with scholarship less than >75 years of study less than >4.
Attach, and tray for help.
No one can help?

[k0st4din]

Hello, you're my only hope for help.
No one can help me with this task?

[Don]

For the scholarship, are you using the "year" column? If so, just click the drop-down arrow and select "Sort Largest to Smallest" to get the student with the highest scholarship.

To get the students that fall in the range of 75 to 4, click the drop-down arrow in the column header, go to Number Filters and then Between... and adjust the options accordingly in the window that opens up.

hope that helps

[k0st4din]

Hello don
I do not know where to start, generally know that this is the easiest way to display the result. But the question is that you should give a result with formulas. Otherwise the filter for 2 seconds I'm ready, but .......
Thank you and see you soon

[Sisyphus]

Hi,
I've looked at your question 3 or 4 times and still don't understand it. Now I know that you don't want a filtered result. You say, you want a result "with formulas". So, I presume you want a result to appear automatically in a cell and, presumably, change as you change values in your list or add(??) to the list. Too many unknowns!

Why don't you take the same sheet you already posted, show us the cells where you want each result. Describe the result in plain language - much like you already did - and some one can figure out a formula to extract it from your data. I didn't understand all your wishes. But had your query been better structured I would have done the part I understood and asked about the others.

Have a pleasant day!

[k0st4din]

Hi
I do not know exactly what you can not understand, so I repeat again, with a little more clarification. The table which I'll attach the applicable H2 (MAX) -> me to get the highest value from column B in H3 use Lookup to return my name in column C, but this happens only if (C) - I is sorted in ascending order, IF Shuffle - I know very well not happen. I'm looking for this formula that when mixed points in column C, somehow my name appear responsible for the highest score. And then search again with a formula to deliver me into a cell (no matter which), the full list of these names from column B that are smaller than 75 (B) and less than 3 (F). Subject headings: 1000 number (A1: E1000). I apologize if you still can not understand me, but I hope this time is specific and precise.

[Don]

Try this formula to get the person with the highest score:

Code: Select all

=INDEX(C2:C13,MATCH(MAX(B2:B13),B2:B13,0))

To get a list of users that fall within 3 and 75, now that is a bit more complicated

Code: Select all

=INDEX($C$2:$C$13,SMALL(IF(($B$2:$B$13>=3)*($B$2:$B$13<=75),ROW($1:$12)),ROW(1:1)),COLUMNS($A1:A100))

This is an array formula, so enter it using Ctrl + Shift + Enter. Copy it down to as many rows as needed to list all of the users that fall within the given criteria, in this case between 75 and 3.

hope this helps

[k0st4din]

Hi don,
Thank you for the first formula, but the second formula does not work the way you look - what I mean: after putting her to deliver me all the names that are less than 75 column C and less than 3 - F. In column I'm attaching the example that I go (I have to go out) -> lllll, yyyyy, ggggggg, reeee, and I only see ->>> lllll. Did not even need to change something.
Thank you very much
Greetings from me and a great day.
And another thing do not you think that in this formula B to be E -->>> INDEX ($C$2:$C$13,SMALL(IF(($E$2:$ E$13>=3)*($B$2:$B$13<=75),ROW($1:$12)),ROW(1:1)),COLUMNS($A1:A100))

[Don]

I might have gotten the columns wrong but it seems like you've found where to change that; the basic formula will still stay the same. Also, make sure to copy the second formula down to multiple cells in order to see multiple users. I'm not sure that it's feasible to have all of the users appear in the same cell separated with a comma using formulas in Excel; that would require VBA, which I would say, since you already have the formulas, is not really needed.

Also, now that I re-read your other post, here is a slightly updated formula:

Code: Select all

=INDEX($C$2:$C$13,SMALL(IF(($E$2:$E$13<3)*($B$2:$B$13<75),ROW($1:$12)),ROW(1:1)),COLUMNS($A1:A100))

Changed B to E and >= to < for E.