VLOOKUP if cell contains a word within the text

Ever had a huge text and want to extract or find a particular word or number in the text. In some cases, it becomes hard to reread the whole text to find out where the word or number is placed within the text. The VLOOKUP command helps a lot in performing such tasks. It enables tasks to be completed easier and fast.

If one master how to use the function and combine it with other functions then they could perform tasks easier. For example in a case where a teacher has marks recorded for many students and they require a particular student's marks, they can work through the long way and go through all the students' marks, or they could use excel with the VLOOKUP command and identify the position in which the student is. This method becomes easier and it is assured if the correct method and instructions are followed. These are the steps to be followed if such a task is allocated;

Step 1

Open a record from your excel files that you would like to work on. If you do not have any record, you can come up with a general data set like the one shown below.

Step 2

From the above data set, we have three columns; the id column, the date column, and the sales description. Our interest is in the sales description column because it is made of texts. The texts have words in between and that is why we want to extract the words. To perform the extraction, we will use the VLOOKUP function. The VLOOKUP function has other sub-functions like the LOOKUP and MID. For us to extract the words, we can use the MIDI function. This sub-function will sample out the word between the texts based on the range you want to use. For our first column, we can the formula as follows. =MID ($C2, 5, 3). Remember the values of the specified number like "3" can be changed depending on the length of the words.

Leave a Comment