# help required on sorting

Hello,

apple 10
grape 2
vine 3
sour 14
apple 2
vine 5
vine 6
apple 8

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
apologies for my wage statement above, what result i would want is :

Last apple count = 8
Last Grape count = 2
Last vine count = 6
Last sour count = 14

Do you mean you want to sort the list by column one and then column two or do you want a function that picks up the last instance of apple in the list and displays the value associated with it?

as above

Here's one way:
Book1
CDEFG
4tom1tom17
5****2
6harry3
7jane4
8polly5
9kelley6
10scott7
11brian8
12tom9
13****10
14harry11
15jane12
16polly13
17kelley14
18scott15
19brian16
20tom17
21****18
22harry19
23jane20
24polly21
25kelley22
26scott23
27brian24
Sheet3

G4: =INDEX(\$D\$4:\$D\$27,MAX(IF(\$C\$4:\$C\$27=F4,ROW(\$C\$4:\$C\$27)-ROW(\$C\$3),0))) confirmed with Ctrl+Shift+Enter... though the formula could be simplified to =INDEX(D:D,MAX(IF(\$C\$4:\$C\$27=F4,ROW(\$C\$4:\$C\$27),0))), also a CSE formula.

Thank you very much it has worked. However i have another additional requirement to this formula. Rather than searching for the full word "TOM" - can we have a formula which checks every string.

For Eg : instead of "Changed category to Global 1st Line GSD" searching as a full word - can i have the parameter "Changed category to" but should still return last value irrespective of what is after that in the search matrix.

Just to simplify further :

Changed category to Global 1st Line GSD - resolved tickets  1
Request escalated to alert level 2  3
Changed read status to: New information  5
Request added to ticket from internal web   6
Changed category to Global 1st Line GSD - resolved tickets  7
Request escalated to alert level 2  9
Changed read status to: New information  11
Request added to ticket from internal web   12
Changed category to Global 1st Line GSD - resolved tickets  13
Request escalated to alert level 2  15
Changed read status to: New information  17
Request added to ticket from internal web   18
Changed category to Global 2nd Line GSD - resolved tickets  19
Request escalated to alert level 2  21
Changed read status to: New information  23
Request added to ticket from internal web   24

Changes Category to = 19

Try this:

Code:
``=INDEX(D:D,MAX(IF(LEFT(\$C\$4:\$C\$27,LEN(F4))=F4,ROW(\$C\$4:\$C\$27),0)))``

Replies
2
Views
72
Replies
1
Views
195
Replies
1
Views
164
Replies
4
Views
231
Replies
16
Views
2K

1,217,476
Messages
6,136,883
Members
450,029
Latest member
MissQuotation

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back