Excel Lookup and Find the 2nd, 3rd, 4th or Nth Occurrence / Match (Without Using an Array Formula)

In this video I demonstrate how to categorise text values based on a list of keywords.

I have a list of course names. If the course name contains words like Excel or Outlook, I want to categorise the course as a Microsoft course. If the course name contains words like InDesign or Premier, I want to categorise the course as an Adobe course.

The functions featured in this video tutorial are SEARCH, MATCH and INDEX.
------------------------

[hanihan-xv]

I was thinking if it was possible to get the second or nth result, but this tutorial answer my curiousity ! It's so easy to understand. I really apreciate and thanks a lot for this awesome tutorial!

[soniagonzales]

This worked perfectly for what I was trying to accomplish! Thank you for posting.

[hymynameisjimmy]

So helpful, thank you!! I looked through at least 10 articles and a few other videos and couldn't quite get what I wanted. This made it (creating a dynamically ranked list with varying numbers of rows) so easy!

[darralsaw]

Really great video and very well explained. I have been looking for a solution for sometime now and this worked great. With the explanations I was able to tweak it to my exact needs too. Thank you 🙂

[sinisterspeed]

Thanks for this!, explained what we're doing when/why. Helped me get through what I was trying to do.

[IvanCortinas_ES]

Great video Chester! Thanks for telling it in various ways.

[nirortal]

Great!!! looking for this solution for months!! thank you!

[bsgoodmail]

Great explanation which i have been looking for! Thank You Sir!!

[MukeshSingh-cwhr]

Thanks a lot. Much needed video well explained

[spg]

Thank you very much for this video, what if I want second last value to be returned in multiple occurances. Please reply early

[ebirollz]

this is so helpful!!! is there a way to specify which occurrence without having it written in the table? for example, if instead of changing the number in the "game" cell to get a specific occurrence, if i could have a column named "game 1" "game 2" etc and have each value in it's own column? sorry if this is a dumb question, I am an excel newbie.

[MohamedAhmed-xvsx]

hello Chester i got Microsoft office 365 proplus and couldn't found the formula filter

[wellingtonpassos]

What if I have multiple criterias? For example, let's say we bring another column for the game month. So, I want to look for the the second game that Barbara played but in January or February...

[jobelow]

Hi there

Thanks for the video! What if I want to find the nth occurrence of a value in a range of cells? For example if the cells contain numbers and text and I want to find the 2nd instance of a cell that includes 4 numbers, like say 1234, and return the entire value of that cell with both the text and numbers?

[yltmde]

I have a question. lets say I have created readable barcode as for Bill = 1 his score = 397, number of games he played = 5 and to make it more complex I will ad number for the District where competition was held and a colour of t-shirt he was wearing - DST=12 and Orange T-shirt = 7 - if I were to generate barcode it will look like this - Index number 5+1+397+5+12+7+01 that will generate (+here is not a sum of numbers but adding values as continued number) 51397512701

lets say I want to lookup how many games the person wearing Orange t-shirt was playing ; I will have to lookup sixth number and nine numbers from the string of numbers, Id like to have them in separate columns as T-shirt in (lets say) F8 and colour G8, I will have to create some kind of reverse lookup that search specific place in the string of numbers but have no idea where to begin. is this something you could help me with ?