Display Data from the same Row if a part of the text in the sentence matches
How to return the relevant data from the same row if a part of the text in the sentence was found
If you are a continuous user of Excel, I believe that you have encountered a problem with the text at least once. In this Excel tutorial I will show how to solve the problem of finding a part of the text in the sentence, using the Asterisk (Wildcards). In this case as a condition, we have a sentence in one cell. The task is, in the database (yellow color), to find part of the sentence text and return the data from the second column in the same row.
In the image below you can see the situation of our task. In the cell range 'A2:C11' are our source data, ie database. In the cell range 'A13:C18' are expected results. Notice that I've been pointing the text in the font color to help you understand this Excel tutorial. Our goal is to set a formula in the 'B14' cell and get the result from 'B2:B11' and 'C2:C11' cell range.
For the first example I will take a sentence in 'A14' cell. This sentence "This Is Asus motherboard" is a condition. Our formula needs to find one word in the sentence that is in the 'A2:A11' cell range and return the data from the same row but adjacent column. Simply put, it is necessary to find any word in the source data and as a result to return the corresponding data from the same row.
For another example I will take the following sentence "This is the best mouse for games". Thus, the formula in this sentence should recognize any word in the database. As you see the result for 'B' the column is "PC" because the formula found the word "mouse" and returned the data from the same row but adjacent to the 'B' column.
Here I want to mention that the present formula, returning data from the first row found if they match any word in the database. So if we have a sentence that contains two terms from the database, the formula will return data for the first word found in the database.
Displaying data from the same row use Asterisk or Wildcards if the formula of the text found in the database
To resolve this task, we can use the following CSE formulas. (Combinations of nested Excel functions are used in these formulas)
ARRAY formula in 'B14' cell is the following:
=IFERROR(INDEX(B$2:B$11;SMALL(IF(ISNUMBER(SEARCH(" "&$A$2:$A$11&" ";" "&$A14&" "));ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1;"");COLUMN($A$1)));"")
The second ARRAY formula we can use is the following:
If you are using Excel 2003 then you can use the nested IF and ISERROR functions instead of the IFERROR function.
Find the word as a condition of the sentence and return the data from the same row
If we have a different problem, ie the opposite of the data as opposed to the first case, then we can use the LOOKUP function. That is, in the database (green color cells) there are sentences in one cell. Our condition is one word. Note that the formula below returns the found result to the first word in the text starting from the last row (ascending).
So in this case, we have different words as conditions. Here is a condition for a single word we are looking for in the database. For example, I'll take the word "Monitor" in the 'A11' cell. Note that the word in question appears in two cells ie two sentences in the database ('A6' and 'A7' cells). The formula below, will search for the word 'Minotor', and when it finds the sentence in which the word is in question, it will return the same row from the adjacent column. If there are multiple sentences in which the relevant word (our condition) is located then the formula will return the data from the first found cell, starting from the last row up.
The formula of the 'B10' cell is as follows: (a copy of the right and down both cell)
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.
Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.